Query with dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 15 Aug 2006 17:16 GMT

Hi all,
I created a form Test (temp name) with 2 comboBox :

Month: SELECT DISTINCT Month([WeddingDate]) AS [Month] FROM orderInfo;

Year: SELECT DISTINCT Year([WeddingDate]) AS [Year] FROM orderInfo;

then I created a query, in the criteria of the field WeddingDate I put :
[Forms]![Test]![Month] And Forms![Test]![Year]

When I run it, I get no results. I tried to run the query with just the
month part and with just the year part and still no results.

What am I doing wrong?

TIA,

Tom

Reply to this Message

Allen Browne - 15 Aug 2006 17:39 GMT

Tom, you are comparing a date field to 2 numbers And'd together? That's not
going to work.

If you must do it that way, try the following for your criteria:
Between DateSerial([Forms]![Test]![Year], [Forms]![Test]![Month],1)
And DateSerial([Forms]![Test]![Year], [Forms]![Test]![Month]+1,0)

There are at least 3 other potential problems with this approach.

1. If the combos are unbound, Access may not understand them as numbers. Set
their Format property to General Number.

2. If the 2 parameters are undeclared, Access may not understand their type
correctly. In query design, choose Parameters on the Query menu, and enter 2
rows into the dialog:
[Forms]![Test]![Month] Long
[Forms![Test]![Year] Long

3. Month and Year are both function names, and will not work reliably.
Here's a list of others to avoid:
http://allenbrowne.com/AppIssueBadWord.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi all,
> I created a form Test (temp name) with 2 comboBox :
[quoted text clipped - 14 lines]
>
> Tom

Reply to this Message

Tom - 15 Aug 2006 18:29 GMT

Thans again, Allen.

Tom

> Tom, you are comparing a date field to 2 numbers And'd together? That's
> not going to work.
[quoted text clipped - 36 lines]
>>
>> Tom

Reply to this Message