Between command

[AirForce]

I have a database which includes a date field (5 June 03) and I would like to be able to get a prompt that ask for the month and year. From the information that is given at the prompt, I would like to output all information for the given month and year.

Thanks,
Newbie

 

[DanChard]

Try this in a query:

select * from your_table
where format(your_date_field,'mmmm-yyyy') = 'June-2003';

Obviously your_table and your_date_field will need to be replaced with the actual table and field names and 'June-2003' is just an example.

If the user is picking the month and year from a form you'll need to consider whether it's best to use text boxes or combo boxes. I'd suggest combo boxes as these give a tighter control over what the user can submit to the query. In order to make the query 'see' what the user has entered onto the form you need to know the control names of the text boxes/ combo boxes that will contain the dates. The default names for these are normally Text1 or Combo18 or whatever but you can find out what they're called and rename them by going to Properties - Other - Name. Assuming you use combo boxes called 'cmbo_month' and 'cmbo_year', you could change the SQL as follows:

select * from your_table
where format(your_date_field,'mmmm') = [Forms]![Form1]![cmbo_month] and (your_date_field,'yyyy') = [Forms]![Form1]![cmbo_year];

This assumes that your form is called 'Form1'.

Hope this is of some use! Please reply if you need any further assistance...