Appending Year and Month in a Date Format

[Guest]

Hi,

I have a drop-down list with years to come and a drop-down list for the 12
months on a form. The user must select a year and a month and that must be
transformed into a date format yyyy/mm/dd so that it can be used in a query.
How do I do it? (The day would be replaced by an asterix, because I want the
query to filter all the days for a certain month and year selected. The month
is a string and must assumeably be changed into a string)

Thanks!

 

[Guest]

Gah, brain freeze!

Rudi, I do know you can set your fields to be date and then assign them as
'yyyy' or 'mm' as needed. However, I cannot think of the needed info to do
this, but I am sure it is listed in the help files...

Sharkbyte

 

[Guest]

Do you think it might be possible to concatenate it and then use the string
to query the table? For instance...say I choos June (month 06) and year 2004
and I add a slash (/) like so...

"2004/06"

and search for all the record that has this. Would this work, even though
the date column is not in a string format, but a string format?

 

[Guest]

If not with a *, you can always concatenate an 01 and a 30/31 and search for
BETWEEN. But I'm pretty sure you have several query options just using the
Year and the Month.

Sharkbyte

 

[Van T. Dinh]

You can simply use the Date Serial function in the Query like:

....
WHERE [YourDateOnlyField]
BETWEEN DateSerial(Forms!YourForm!cboYear, Forms!YourForm!cboMonth, 1)
AND DateSerial(Forms!YourForm!cboYear, Forms!YourForm!cboMonth + 1, 0)

The bound Column of cboYear and cboMonth should be numeric.

 

[Guest]

I take it that the statement below must be included in the criteria part of
my date field in my query. What should I write in the "YourDateOnlyField"?
Should I write the name of the field again?

Thanks!

You can simply use the Date Serial function in the Query like:

....
WHERE [YourDateOnlyField]
BETWEEN DateSerial(Forms!YourForm!cboYear, Forms!YourForm!cboMonth, 1)
AND DateSerial(Forms!YourForm!cboYear, Forms!YourForm!cboMonth + 1, 0)

The bound Column of cboYear and cboMonth should be numeric.

--
HTH
Van T. Dinh
MVP (Access)

Do you think it might be possible to concatenate it and then use the string
to query the table? For instance...say I choos June (month 06) and year 2004
and I add a slash (/) like so...

"2004/06"

and search for all the record that has this. Would this work, even though
the date column is not in a string format, but a string format?

 

[Van T. Dinh]

The name of your Date Field.

What I posted works fine if you Field values have zero time component. If
your Field values have non-zero time components, you need to use slightly
more complex expressions than what I posted. Hence I use the assumed Field
name "YourDateOnlyField".