Query with dates

[Tom]

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

 

[Allen Browne]

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

 

[Tom]

Thans again, Allen.

Tom

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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