Select query with a date as param

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi

I have a table where some data about cars is stored, amongst them MOT
now I would like to find out which cars require new MOT the following month
calculated from today's date (month)

It's set as criteria in the MOT field in a query and should come up with
e.g. car registration number and the MOT date

Month([Car]![MOT])>Now()+Month(1)

this here - sadly - doesn't give me any results :(

Thanks for any syntax for my criteria or other suggestions.

Gina
 
Hi Gina,

Create an extra column in your query as follows

MonthsToMOT: DateDiff("m",[Car]![MOT],Now())

In the criteria for this column put 1

This will show you which cars are due an MOT next month. It also caters for
today being December where the MOT is due in Jauary next year.

Dennis.
 
Thanks Dennis
.... had to type in -1 to get a result

great :) what would I do without such valuable hands-on advice
probably panic
Gina

Dennis said:
Hi Gina,

Create an extra column in your query as follows

MonthsToMOT: DateDiff("m",[Car]![MOT],Now())

In the criteria for this column put 1

This will show you which cars are due an MOT next month. It also caters for
today being December where the MOT is due in Jauary next year.

Dennis.

Gina said:
Hi

I have a table where some data about cars is stored, amongst them MOT
now I would like to find out which cars require new MOT the following month
calculated from today's date (month)

It's set as criteria in the MOT field in a query and should come up with
e.g. car registration number and the MOT date

Month([Car]![MOT])>Now()+Month(1)

this here - sadly - doesn't give me any results :(

Thanks for any syntax for my criteria or other suggestions.

Gina
Click to expand...
Click to expand...
 
Thanks Dan

your syntax gives me the months (in negative numbers) with the MOT field
Now I have a selection of syntax!!!
such a relieve to see it working after I was struggeling again & again!!

Gina

Dan Artuso said:
Hi,
Try:
Month([Car]![MOT])>Month(Date()) + 1


--
HTH
Dan Artuso, Access MVP


Hi

I have a table where some data about cars is stored, amongst them MOT
now I would like to find out which cars require new MOT the following month
calculated from today's date (month)

It's set as criteria in the MOT field in a query and should come up with
e.g. car registration number and the MOT date

Month([Car]![MOT])>Now()+Month(1)

this here - sadly - doesn't give me any results :(

Thanks for any syntax for my criteria or other suggestions.

Gina
Click to expand...
Click to expand...
 
Back
Top