|
What's wrong with WinForms? They are great if you need to build a quick utility. I like WPF if i'm working on a large project, but WinForms are just too quick & easy.
|
|
|
|
|
83. Support church melody (5)
|
|
|
|
|
|
yup 
|
|
|
|
|
Hmmmm,
If this became a drive-through service you could serve 240 customers per day. 
Best Wishes,
-David Delaune
|
|
|
|
|
Yeah, but wouldn't be worth it for ten yup 's an hour...
|
|
|
|
|
|
|
Ya
|
|
|
|
|
Car on wheels does this (5)
|
|
|
|
|
|
ya
modified 5-Apr-19 1:01am.
|
|
|
|
|
Hi all. I have two tables in access (VB6): SupplierInvoices & Sales. They both have 3 common field names: Date, Code & Qty. I need to query both tables via SQL query by a specific date range as follows (I know this is wrong, but just to give you an idea):
rs.Open "Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as tTotal from SupplierInvoices, format(Sales.Date, 'dd-MMM-yyyy') as sDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(SupplierInvoices.Date, 'dd-MMM-yyyy');", cn, adOpenKeyset, adLockOptimistic
The result needs to return from both tables the sum of the qty fields for that date range. I can't get it right! Please help.
modified 5-Apr-19 0:46am.
|
|
|
|
|
|
|
I don't have any experience with Access, but from what I know from SQL server, I think when you have group by you are allowed to have in the select the aggregate and the group by field. The problem is that you have a group by from one of the tables and put other fields in the select. I think you have to use Union and get it in separate set of rows. And from that set you can try to select in an outer select. Not sure if this is possible in Access.
modified 20-Oct-19 21:02pm.
|
|
|
|
|
I have tried this but get Group by error:
rs2.Open "Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as lTotal from SupplierInvoices where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Union Select distinct format(Sales.Date, 'dd-MMM-yyyy') as tDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "#;", cn, adOpenKeyset, adLockOptimistic
|
|
|
|
|
I mean something like:
select SupplierInvoices.Date as tDate, Sum(SupplierInvoices.QTY) as lTotal, 'supplier' as tablename
group by tdate,tablename
Union
select other table same thing
I can't test it, but I would try something like that
modified 20-Oct-19 21:02pm.
|
|
|
|
|
Thank you my good man. I am almost there!!! except I get an error saying item rs2!sTotal cannot be found:
rs2.Open "Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as lTotal from SupplierInvoices where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(SupplierInvoices.Date, 'dd-MMM-yyyy') Union Select distinct format(Sales.Date, 'dd-MMM-yyyy') as tDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(Sales.Date, 'dd-MMM-yyyy');", cn, adOpenKeyset, adLockOptimistic
|
|
|
|
|
The error you are getting is because in Union the column names have to be the same. That's why you need another column tablename to distinguish between them
I would also try to put in the first original query
Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as tTotal from SupplierInvoices, format(Sales.Date, 'dd-MMM-yyyy') as sDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by tDate, sDate
Wouldn't that work too?
modified 20-Oct-19 21:02pm.
|
|
|
|
|
I tried it but says error in from clause.
I appreciate your help.
|
|
|
|
|
I also tried:
rs2.Open "Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as tTotal, format(Sales.Date, 'dd-MMM-yyyy') as sDate, Sum(Sales.QTY) as sTotal from SupplierInvoices, Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by tDate, sDate;", cn, adOpenKeyset, adLockOptimistic
but it says "the field PLU could refer to more than one...."
|
|
|
|
|
Ah poor Bruno, this weeks lame duck.
Asking a programming question in the Lounge where it clearly states not to.
Doesn't know enough netiquette not the SHOUT.
Having to support a VB6 application more than 20 years after it's EOL.
Using Access as the database
Man you have my sincere sympathies.
The only thing to make that worse would be to be using Crystal Reports as well.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm the orphan in the storm, yes!
But hey, I am trying!
|
|
|
|
|
I am using a SSD to hold code and (until Sunday) databases that were attached to the sql instance on my desktop and my laptop. Everything was fine until Sunday night, when I think I did some steps in the wrong order on my desktop regarding detaching/attaching databases. In the process of doing something wrong, I lost all of the database work I had done over the weekend. Half a dozen tables and almost a dozen stored procs - gone - and could not be recovered.
Today, I finished reinventing all the database crap I lost, and implemented a new policy. I create two jobs - one that backs up all the databases (that are not system databases) to the SSD, and one that restores the backed up databases from the SSD. Now all I have to do is remember to run them (and not run the wrong one when I run one).
BTW, my template project is going quite well, and I'm almost ready to make the demo video (hopefully by sometime next week.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
