error in the code written in criteria of design query?

wavemehello

Board Regular
Joined
Jan 24, 2006
Messages
221
-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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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

Board Regular
Joined
Jan 24, 2006
Messages
221
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
Select * from sheet2
where month([dob])=Month(Date())+1

hth,
Giacomo
 

wavemehello

Board Regular
Joined
Jan 24, 2006
Messages
221

ADVERTISEMENT

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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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,
 

Forum statistics

Threads
1,136,346
Messages
5,675,229
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top