Complex Query - Date Comparison

[Rich]

Vba references

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

 

[michaelfairburn]

Thanks Rich, but there isn't any vba in the database (or at least none that I've put in there)

 

[Rich]

You may not have written any vba functions but vba is a part of access and many functions within access won't work if it hasn't loaded all the required parts, check out the link again, it goes into greater detail

 

[michaelfairburn]

I read the article Rich and I'll check out the database on Monday when I'm back at work. Thanks.

Mike

 

[michaelfairburn]

Try and aliase an extra field for each Union:

SELECT tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PM Management], tblLinkedMasterData.[Program Name1], tblLinkedMasterData.[RFD Initial Date], "Initial" As Gate
FROM tblLinkedMasterData
WHERE (((tblLinkedMasterData.[RFD Initial Date])>=Now() And (tblLinkedMasterData.[RFD Initial Date])<=Now()+14))

UNION
SELECT tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PM Management], tblLinkedMasterData.[Program Name1], tblLinkedMasterData.[RFD Revised Date], "Revised" As Gate
FROM tblLinkedMasterData
WHERE (((tblLinkedMasterData.[RFD Revised Date])>=Now() And (tblLinkedMasterData.[RFD Revised Date])<=Now()+14)

JR

Thanks JR, that worked a treat. Much appreciated

 

[JANR]

Excelent, glad to help

JR

 

[Brianwarnock]

I must state again that using NOW() means that you may miss records as the Time will be taken into account.

If your table contains time you may miss them on the first and last days, if not only, on the first day.

Brian