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.Date FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.TransactionType)) = 'Inventory') And ((First(TransactionLog.ProductModelNumber)) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) ORDER BY TransactionLog.Date;"

How can I change the RowSource to this and display the [Date] as "mmm-dd-yyyy"?

Thanks!

NeoTeq

Try this:

[FromDate].RowSource = "SELECT Format([Date],""mmm-dd-yyyy"") AS FromDateTransactionLog.Date FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.TransactionType)) = 'Inventory') And ((First(TransactionLog.ProductModelNumber)) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) ORDER BY TransactionLog.Date;"

SOTA

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)

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NeoTeq

Date is reserved... I've included the tablename, please give this one a try. I'm wondering why you've named the datefield "FromDateTransactionLog.Date"? Maybe you should replace that, too.

[FromDate].RowSource = "SELECT Format([TransactionLog].[Date],""mmm-dd-yyyy"") AS FromDateTransactionLog.Date FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.TransactionType)) = 'Inventory') And ((First(TransactionLog.ProductModelNumber)) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) ORDER BY TransactionLog.Date;"

With different name, if above doesn't work:

[FromDate].RowSource = "SELECT Format([TransactionLog].[Date],""mmm-dd-yyyy"") AS FromDate FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.TransactionType)) = 'Inventory') And ((First(TransactionLog.ProductModelNumber)) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) ORDER BY TransactionLog.Date;"

nico5038

Try:

[FromDate].RowSource = "SELECT Format([Date],"mmm-dd-yyyy") AS LogDate FROM TransactionLog GROUP BY TransactionLog.Date HAVING (((First(TransactionLog.TransactionType)) = 'Inventory') And ((First(TransactionLog.ProductModelNumber)) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) 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)

SOTA

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.TransactionType)) = 'Inventory') And ((First(TransactionLog.ProductModelNumber)) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) ORDER BY TransactionLog.TALDate;"

NeoTeq

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.TransactionType)) = 'Inventory') And ((First(TransactionLog.ProductModelNumber)) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) ORDER BY TransactionLog.TALDate;"

SOTA

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)

SOTA

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...

ASKER CERTIFIED SOLUTION

NeoTeq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOTA

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.TransactionType)) = 'Inventory') And ((First(TransactionLog.ProductModelNumber)) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) ORDER BY Format([TALDate],""mmm-dd-yyyy"");"

However, the query returned nothing. I am guessing that the time values are now messing this up.

SOTA

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.ProductModelNumber) = [Forms]![InventoryTransactionReportPreSelect]![ProductModelNumber])) GROUP BY Format([TALDate],""mmm-dd-yyyy"") HAVING (((First(TransactionLog.TransactionType)) = '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.

NeoTeq

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?