Query date criteria

[davoakey]

How do I put criteria in to get the the birthdays in the next 30 from a date of birth field

 

[davoakey]

I ran an update query and changed all the fields from null. and still get mismatch.
I have the image of the sql on my clip board, but cannot see how to let you see it as the paste is not avialible in this window, I could email it but how to get your address or let you have mine without publishing it here, is a problem
Regards
Dave

 

[davoakey]

The message I get is "Date mismatch in criteria expression

 

[dhookom]

What did your update query change the null value to?

You should view the SQL View of your query. This will look much like notepad and allow you to select and copy the "text" of the sql statement and paste it into a reply.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[davoakey]

Ok Wiil thats a good idea

 

[davoakey]

SELECT Contacts.PersonID, Contacts.FirstName, Contacts.LastName, Contacts.Dear, Contacts.Address, Contacts.City, Contacts.County, Contacts.PostalCode, Contacts.HomePhone, Contacts.WorkPhone, Contacts.WorkExtension, Contacts.MobilePhone, Contacts.EmailName, Contacts.Birthdate, DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate])) AS Birthdays
FROM Contacts
WHERE (((DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate]))) Between Date() And DateAdd("d",30,Date())));

Hi It worked

 

[davoakey]

By the way, I updated the Birthdate field to 0

 

[dhookom]

Since you seem to have solved this, I hope you post this in the public Access news group...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[davoakey]

Sorry, I have not solved the problem, I was refering to the pasting which worked, sorry aboout that, but looking back on it, I can see it looks as if I had cracked it, but I still am no further forward
Regardss
Dave

 

[dhookom]

davoakey,
When you view your table in datasheet view, does the Birthdate field look left or right aligned? Also, I am a bit confused about why you updated the field to 0. What value displays in the records that were updated to 0?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[davoakey]

I up dated to 0 because a null propogates through and expression, and you suggested a null value might be the problem.
In dataview, the field with no date are blank and the fields with dates are aligned right

 

[dhookom]

I thought you said you updated all the blank dates to 0? If so, you should see 12/30/1899.
Try

SELECT Contacts.PersonID, Contacts.FirstName,
Contacts.LastName, Contacts.Dear, Contacts.Address, 
Contacts.City, Contacts.County, Contacts.PostalCode, 
Contacts.HomePhone, Contacts.WorkPhone, 
Contacts.WorkExtension, Contacts.MobilePhone, 
Contacts.EmailName, Contacts.Birthdate, 
DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate])) AS Birthdays
FROM Contacts
WHERE Birthdate is Not Null AND 
DateSerial(Year(Date()),Month([Birthdate]),Day([Birthdate])) Between Date() And DateAdd("d",30,Date());

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[davoakey]

Hi dhookom
Sorry to be a pain, but I am not sure how to use the SQL you posted.
What goes in the calculating field and whatgoes is the criteria.
I tried pasting the lot into the SQL window, and it did not work, so that is not the way.
I assumed the up date query worked as it did not throw up a error when I ran it, but the fields without dates were definiatly blank, so I suppose they must be null, wwhen I put the cursor in the date field the date format appears.
Regards
Dave

 

[dhookom]

You should have been able to paste my code into a blank SQL view of a query. When you state [red]and it did not work[/red], you should provide what you did and the error message or result or whatever. It is very difficult to see your database from here.

All my sql should have done is change the criteria under the Birthdate field to:
Is Not Null


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[davoakey]

Hi dhookom
I had another go at pasting the code in, and it all seemed to work Ok, I did not get an error message, but it did not show any records.
The calculating field and criteria were in and the "is not null" was in the birthdate field criteria.
If it is possible for you to go to the web page

http://www.ox18.com

and use the feed back form which will come to me, I will respond with my email address, and I will be able to send you a jpeg version of what I am doing.
Regards
Dave

 

[dhookom]

done

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

Update. Dave emailed a zipped mdb to me. There were no date values in the BirthDate field. I update most of the records with random birthdates and ran the query that he had in the file he sent to me. The query worked exactly as expected. I believe the sql was the same as the code I provided in an earlier message in this thread.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[davoakey]

Thanks dhookom, I put a couple of dates in to try it, and it would not work, but it sure does now, thanks very much. You suggested that for the labels I use a criteria drop down list, that is ok I could do that, but how would I coop with a person who qualified for two or three criterias
regards
Dave

 

[dhookom]

I'm not sure what you mean by criteria drop down list. Does this pertain to my suggestion regarding the four categories? If so, you should start a new thread in the "Access Tables and Relationships" forum. Provide a bit of your Contacts table structure and ask for advice.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[davoakey]

Ok will do
Thanks again