Date problem

[Tom]

Hi all,

I have a field with date - wedding dates.
I want to create a query on that field with 2 criterias: month and year.

so, basiclly I will have a form with 2 combobox, 1 for month and 1 for year
and when I'll run the query I'll get the list of all the weddings of that
month(and year).

I think it possible with the function date serial, but I couldn't manage.

Thanks for any help,

Tom

 

[gibsonsgman]

Hi all,

I have a field with date - wedding dates.
I want to create a query on that field with 2 criterias: month and year.

so, basiclly I will have a form with 2 combobox, 1 for month and 1 for year
and when I'll run the query I'll get the list of all the weddings of that
month(and year).

I think it possible with the function date serial, but I couldn't manage.

Thanks for any help,

Tom

Tom,
You are going to have to do this in SQL and it shouldnt be too hard.
Try this:
dim strMonth as string
dim strYear as string
dim SQL as string
dim db as database
dim qd as querydef

set db=currentdb

set qd=db.createquerydef("qry_Wed_Dates")

strMonth=cboMonth.text
strYear=cboYear.text

SQL="SELECT tblWeddings.* FROM tblWeddings WHERE tblWeddings.Date Like
'*" & strMonth & "*' AND tblWeddings.Date Like '*" & strYear & "*';"

qd.SQL=SQL

or alternatively, you could just alter the record source of the form
you are working on and cut out a couple of steps above (depends on
what you are trying to). all you would need is the following:

dim strMonth as string
dim strYear as string
dim SQL as string

strMonth=cboMonth.text
strYear=cboYear.text

SQL="SELECT tblWeddings.* FROM tblWeddings WHERE tblWeddings.Date Like
'*" & strMonth & "*' AND tblWeddings.Date Like '*" & strYear & "*';"

tblWeddings.RecordSource=SQL

Either of those should work, but keep in mind i didn't test this code,
it was written on the fly. if it doesn't work or you find a better
solution let me know.

~Brian

 

[Allen Browne]

In query design view, type this into a fresh column in the Field row:
TheYear: Year([wedding date])
Substitute your field name if it is not called "wedding date".

In the next column:
TheMonth: Month([wedding date])

You can now apply any criteria or filters you want on these fields.

 

[Tom]

Thank you both.
Allen,
Thanks, it`s exactly what I needed.

Tom

In query design view, type this into a fresh column in the Field row:
TheYear: Year([wedding date])
Substitute your field name if it is not called "wedding date".

In the next column:
TheMonth: Month([wedding date])

You can now apply any criteria or filters you want on these fields.

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

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

I have a field with date - wedding dates.
I want to create a query on that field with 2 criterias: month and year.

so, basiclly I will have a form with 2 combobox, 1 for month and 1 for
year and when I'll run the query I'll get the list of all the weddings of
that month(and year).