?
Solved

Usinf Format inside SQL Statement?

Posted on 2005-03-13
13
Medium Priority
?
1,190 Views
Last Modified: 2008-01-09
[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!
0
Comment
Question by:SOTA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 10

Expert Comment

by:NeoTeq
ID: 13529205
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;"
0
 

Author Comment

by:SOTA
ID: 13529240
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)
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 13529265
Couple of somewhat random thoughts...

(1)  Access has a Date function, which returns the date on your system clock.  Naming a field Date is not the best idea, as Access may confuse it with the function.  Better to name id dt_start for Start Date, dt_sale, for Sale Date, you get the idea.
(2)  Using SQL within a query is not very optimized, and it doesn't allow you to re-use it without typing the SQL.  I recommend you take your SQL string and make a query out of it (when I do this, I prefix all of these queries with qcbo_).  That way, you can test the query to make sure it's perfect, then just feed the query name into FromDate.RowSource.

>SELECT Format([Date],"mmm-dd-yyyy") AS FromDateTransactionLog.Date
Also, it's not a good idea to use periods ( . ) in form names.  Having spaces or punctuation marks in them usually forces you to surround the name in square brackets like [FromDateTransactionLog.Date]

Hope this helps.
-Jim
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 10

Expert Comment

by:NeoTeq
ID: 13529279
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;"
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13529290
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)
0
 

Author Comment

by:SOTA
ID: 13529336
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;"
0
 
LVL 10

Expert Comment

by:NeoTeq
ID: 13529355
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;"
0
 

Author Comment

by:SOTA
ID: 13529409
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)
0
 

Author Comment

by:SOTA
ID: 13529429
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...
0
 
LVL 10

Accepted Solution

by:
NeoTeq earned 200 total points
ID: 13529432
[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"");"

Sorry, forgot about the order by!
0
 

Author Comment

by:SOTA
ID: 13529471
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.
0
 

Author Comment

by:SOTA
ID: 13529511
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.
0
 
LVL 10

Expert Comment

by:NeoTeq
ID: 13529535
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?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question