SOTA
asked on
Usinf Format inside SQL Statement?
[FromDate] is a ComboBox. This gives me an error at this point "mmm-dd-yyyy":
[FromDate].RowSource = "SELECT Format([Date],"mmm-dd-yyyy ") AS FromDateTransactionLog.Dat e FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.Tr ansactionT ype)) = 'Inventory') And ((First(TransactionLog.Pro ductModelN umber)) = [Forms]![InventoryTransact ionReportP reSelect]! [ProductMo delNumber] )) ORDER BY TransactionLog.Date;"
How can I change the RowSource to this and display the [Date] as "mmm-dd-yyyy"?
Thanks!
[FromDate].RowSource = "SELECT Format([Date],"mmm-dd-yyyy
How can I change the RowSource to this and display the [Date] as "mmm-dd-yyyy"?
Thanks!
ASKER
I did and I got this error:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. (Error 3141)
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. (Error 3141)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Date is reserved... I've included the tablename, please give this one a try. I'm wondering why you've named the datefield "FromDateTransactionLog.Da te"? Maybe you should replace that, too.
[FromDate].RowSource = "SELECT Format([TransactionLog].[D ate],""mmm -dd-yyyy"" ) AS FromDateTransactionLog.Dat e FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.Tr ansactionT ype)) = 'Inventory') And ((First(TransactionLog.Pro ductModelN umber)) = [Forms]![InventoryTransact ionReportP reSelect]! [ProductMo delNumber] )) ORDER BY TransactionLog.Date;"
With different name, if above doesn't work:
[FromDate].RowSource = "SELECT Format([TransactionLog].[D ate],""mmm -dd-yyyy"" ) AS FromDate FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.Tr ansactionT ype)) = 'Inventory') And ((First(TransactionLog.Pro ductModelN umber)) = [Forms]![InventoryTransact ionReportP reSelect]! [ProductMo delNumber] )) ORDER BY TransactionLog.Date;"
[FromDate].RowSource = "SELECT Format([TransactionLog].[D
With different name, if above doesn't work:
[FromDate].RowSource = "SELECT Format([TransactionLog].[D
Try:
[FromDate].RowSource = "SELECT Format([Date],"mmm-dd-yyyy ") AS LogDate FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.Tr ansactionT ype)) = 'Inventory') And ((First(TransactionLog.Pro ductModelN umber)) = [Forms]![InventoryTransact ionReportP reSelect]! [ProductMo delNumber] )) ORDER BY TransactionLog.Date;"
I agree with the bad naming, but the problem is the fact that the Format should "deliver" the field in a "differently" named column. Now you reference your Date twice....
Nic;o)
[FromDate].RowSource = "SELECT Format([Date],"mmm-dd-yyyy
I agree with the bad naming, but the problem is the fact that the Format should "deliver" the field in a "differently" named column. Now you reference your Date twice....
Nic;o)
ASKER
I decided to change the "Date" field to "TALDate".
Now, I tried this and it worked to some degree. It formatted perfectly but it did not Group the query.
[FromDate].RowSource = "SELECT Format([TALDate],""mmm-dd- yyyy"") AS LogDate FROM TransactionLog GROUP BY TransactionLog.TALDate HAVING (((First(TransactionLog.Tr ansactionT ype)) = 'Inventory') And ((First(TransactionLog.Pro ductModelN umber)) = [Forms]![InventoryTransact ionReportP reSelect]! [ProductMo delNumber] )) ORDER BY TransactionLog.TALDate;"
Now, I tried this and it worked to some degree. It formatted perfectly but it did not Group the query.
[FromDate].RowSource = "SELECT Format([TALDate],""mmm-dd-
Could that be because TALDate is a datetime, and there are time values stored? If so, try formatting the date in the group by clause:
[FromDate].RowSource = "SELECT Format([TALDate],""mmm-dd- yyyy"") AS LogDate FROM TransactionLog GROUP BY Format([TALDate],""mmm-dd- yyyy"") HAVING (((First(TransactionLog.Tr ansactionT ype)) = 'Inventory') And ((First(TransactionLog.Pro ductModelN umber)) = [Forms]![InventoryTransact ionReportP reSelect]! [ProductMo delNumber] )) ORDER BY TransactionLog.TALDate;"
[FromDate].RowSource = "SELECT Format([TALDate],""mmm-dd-
ASKER
Yes, time values are stored! Re-tried it and got:
You tried to execute a query that doesn't include the specified expression "TransactionLog.TALDate" as part of an aggregate function. (Error 3122)
You tried to execute a query that doesn't include the specified expression "TransactionLog.TALDate" as part of an aggregate function. (Error 3122)
ASKER
I might opt to make a Query then feed the query name into FromDate.RowSource as JimHorn suggests.
So, how do I feed the query name into [FromDate].RowSource in code? Sorry for my novice understanding of all this...
[FromDate].RowSource = "FromDateQuery1"
of course does not work...
So, how do I feed the query name into [FromDate].RowSource in code? Sorry for my novice understanding of all this...
[FromDate].RowSource = "FromDateQuery1"
of course does not work...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Did this:
[FromDate].RowSource = "SELECT Format([TALDate],""mmm-dd- yyyy"") AS LogDate FROM TransactionLog GROUP BY Format([TALDate],""mmm-dd- yyyy"") HAVING (((First(TransactionLog.Tr ansactionT ype)) = 'Inventory') And ((First(TransactionLog.Pro ductModelN umber)) = [Forms]![InventoryTransact ionReportP reSelect]! [ProductMo delNumber] )) ORDER BY Format([TALDate],""mmm-dd- yyyy"");"
However, the query returned nothing. I am guessing that the time values are now messing this up.
[FromDate].RowSource = "SELECT Format([TALDate],""mmm-dd-
However, the query returned nothing. I am guessing that the time values are now messing this up.
ASKER
Success!! It seems that I need to re-format to use WHERE:
[FromDate].RowSource = "SELECT Format([TALDate],""mmm-dd- yyyy"") AS FromDate FROM TransactionLog WHERE (((TransactionLog.ProductM odelNumber ) = [Forms]![InventoryTransact ionReportP reSelect]! [ProductMo delNumber] )) GROUP BY Format([TALDate],""mmm-dd- yyyy"") HAVING (((First(TransactionLog.Tr ansactionT ype)) = 'Inventory')) ORDER BY Format([TALDate],""mmm-dd- yyyy"");"
Also:
[FromDate].RowSource = "FromDateQuery1"
now works since I changed to WHERE in the query as well.
I will increase to 100 points and split between NeoTeq and JimHorn.
[FromDate].RowSource = "SELECT Format([TALDate],""mmm-dd-
Also:
[FromDate].RowSource = "FromDateQuery1"
now works since I changed to WHERE in the query as well.
I will increase to 100 points and split between NeoTeq and JimHorn.
I don't know... are you sure the query is supposed to return anything at this point? I mean, are there rows with the given productmodelnumber and transactiontype?
[FromDate].RowSource = "SELECT Format([Date],""mmm-dd-yyy