Appending Year and Month in a Date Format

R

Rudi

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!
 
S

Sharkbyte

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
 
R

Rudi

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?
 
S

Sharkbyte

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
 
V

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

Rudi

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!


Van T. Dinh said:
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)


Rudi said:
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?
Click to expand...
Click to expand...
 
V

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