Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 23rd, 2005, 07:50 PM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

when you say sql, do you mean the one that you told me to cut and paste into the column?

 
Old February 23rd, 2005, 07:53 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

When you have your query in design view, go to sql view. it will show you the whole sql statement.

dartcoach
 
Old February 23rd, 2005, 08:02 PM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT Members.[First name], Members.[Last name], Members.[Address 1], Members.[Address 2], Members.City, Members.County, Members.PostCode, Members.[Date of birth], Members.[Telephone number], Members.[Mobile number], Members.Email, DateDiff("yyyy",[Date of birth],Date())+CInt(DateSerial(Year(Date()),Month([Date of birth]),Day([Date of birth]))>Date()) AS Expr1
FROM Members
WHERE (((DateDiff("yyyy",[Date of birth],Date())+CInt(DateSerial(Year(Date()),Month([Date of birth]),Day([Date of birth]))>Date()))<=17));

I HOPE THIS HELPS!

 
Old February 23rd, 2005, 08:09 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

can you send me the sql from the query that works?

dartcoach
 
Old February 23rd, 2005, 08:11 PM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT Members.[Last name], Members.[First name], Awards.Title
FROM Members INNER JOIN (Awards INNER JOIN [Member/award] ON Awards.AID = [Member/award].AID) ON Members.MID = [Member/award].MID
WHERE (((Awards.Title)="First Aid Certificate") AND ((DateDiff("yyyy",[Date of birth],Date())+CInt(DateSerial(Year(Date()),Month([Date of birth]),Day([Date of birth]))>Date()))>=18)) OR (((Awards.Title)="MLTB -Summer Award"));

 
Old February 23rd, 2005, 08:16 PM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Check this out! I have just created a report and the <=17 query worked. How bizzare

 
Old February 23rd, 2005, 08:16 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Try to paste this over the sql in sql view:
SELECT Members.[First name], Members.[Last name], Members.[Address 1], Members.[Address 2], Members.City, Members.County, Members.PostCode, Members.[Date of birth], Members.[Telephone number], Members.[Mobile number], Members.Email, DateDiff("yyyy",[Date of birth],Date())+CInt(DateSerial(Year(Date()),Month([Date of birth]),Day([Date of birth]))>Date()) AS Expr1
FROM Members
WHERE (DateDiff("yyyy",[Date of birth],Date())+CInt(DateSerial(Year(Date()),Month([Date of birth]),Day([Date of birth]))>Date()))<=17;

See if that works.




dartcoach
 
Old February 23rd, 2005, 08:17 PM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have just tried to open the report and it still says data mismatch, however the report then opens with the correct details of under 17s

 
Old February 23rd, 2005, 08:19 PM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It still says the same, data mismatch!!!

 
Old February 23rd, 2005, 08:25 PM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dartcoach, thanks for your help tonight, i am going to have to go to bed. Prob a good job. you can have some peace and quiet. Dont worry though, i will be back on tomorrow!!! Its a shame i couldnt just past my little data base on here for you to check it out!










Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.