error in the code written in criteria of design query?

[wavemehello]

-I have a query in design view
-right click, build, expression and this is what i typed

SELECT * FROM table1
WHERE month(dob)=month(date)+1

It seems incorrect.
(I am trying to query the records whose DOB field has month equal to coming month of system date)

Can someone assist me with the code?

 

[Oorang]

You need to enclose your field names in brackets (ex: [dob]) and if you are using the date function you need to use "date()" not "date" (if "date" is a fieldname it needs to be "[date]").

 

[wavemehello]

Hi Oorang,

this is what I typed

Select * from sheet2
where month[dob]=Month("Date()")+1

and this is the error message when I save and try to run the query.

'the syntax of the subquery in this expression is incorrect check the subquery's syntax and enclose the subquery in parenthesis'

 

[giacomo]

Select * from sheet2
where month([dob])=Month(Date())+1

hth,
Giacomo

 

[wavemehello]

Thank you!!
That worked exactly! Now I have another little problem here.
I displayed the DOB in the query. I only want to show the dd/mm not yeare. For this I went to format and changed it as dd/mm but when I short the records based on the DOB, it shorts with year also, I only want to show the reocords sorted on the basis of day and to display dd/mm not yyyy. How can I do this?

I would be very very thankful!!

 

[giacomo]

instead of select * you'll have to select each field individually then replace [DOB] with format([DOB],"dd/mm") and choose sort ascending on that field.

hth,
Giacomo

 

[VicRauch]

Two things I have noticed in reading this thread.
1. By adding 1 to the month from [dob] you will end up with a month of 13 when someone was born in Dec. I would suggest you test for a 13 and then change the 13 to a 1.
2. If you format the date to dd/mm (which is fine), I would suggest you also format the date to mm/dd into another column so you can sort by month, then day, but still display the dd/mm as you wish.

HTH,

 

[giacomo]

By adding 1 to the month from [dob] you will end up with a month of 13...

thanks for catching that Vic!