Different Date Formats 1

[MarkNie]

Hi

Just need a little clarification on the following:

I have a 2 tables one which has a date field in with the format dd/mm/yyyy, the other has a date format of dd/mm/yyyy/hh:mm:ss.

The second table is used for signing off reports which gives the format as dd/mm/yyyy/hh:mm:ss.

I have created a querie which compares the 2 fields and gives me all the account codes and dates which have not been signed off in time. It works fine the only problem I have is that because of the time it shows all the reports for today too which I don't want to see.

Do you by any chance know any way around this or is it set in stone that if the one field has a date and the other has a date and time that it will show everything for today.

The querie I am using is as follows:

INSERT INTO tblrptDistributionDeadlineMissed ( PortfolioCode, ClientName, [CR Analyst], ProductType, [CRG Deadline], [Distribution Date], Intls, Comment )
SELECT tblTracking.PortfolioCode, tblTracking.ClientName, tblTracking.[CR Analyst], tblTracking.ProductType, tblTracking.[CRG Deadline] AS [Distribution Deadline], qryrptDistributionDate.Date AS [Distribution Date], qryrptDistributionDate.Intls, qryDistributionDateMissed.Comment
FROM (tblTracking LEFT JOIN qryrptDistributionDate ON tblTracking.TrackingID = qryrptDistributionDate.TrackingID) LEFT JOIN qryDistributionDateMissed ON tblTracking.PortfolioCode = qryDistributionDateMissed.PortfolioCode
WHERE (((tblTracking.[CRG Deadline])<Now()) AND ((qryrptDistributionDate.Date)>[tblTracking]![CRG Deadline]))
ORDER BY tblTracking.[CRG Deadline]
WITH OWNERACCESS OPTION;


Any help will be greatly appreciated
Kind regards
Mark

 

[MarkNie]

Hi let me try this again.

I got it to work to not show todays date but the other problem I am having is that because the sign off date field has a time in it and the other table date field has no time in it. The expression I am using is as follows:

>[tblTracking]![CRG Analyst Deadline]

What is happening is because of the time it is showing the dates that have the same value as follows:

CRG Analyst Deadline Analyst Hit Date
07/07/2006 07/07/2006 10:06:26

I don't want it to show them I only want the ones that where signed off the following day as follows:

CRG Analyst Deadline Analyst Hit Date
10/07/2006 11/07/2006 12:05:38

If you have any idea how to do this that would be great as it is driving me mad.

I can't put a time in the first field as it is valid for the whole day. I have tried to put in a time as 23:00:00 but then some of my other reports don't work.

Thanks
Regards
Mark

 

[IT4EVR]

Try in this mode:

WHERE Format([Field1], "Short Date") > Format([Field2], "Short Date")

This way you are comparing two fields that are in short date format.

 

[PHV]

You may try this WHERE clause:
WHERE tblTracking.[CRG Deadline]<Date() AND Int(qryrptDistributionDate.Date)>tblTracking.[CRG Deadline]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkNie]

Hi IT4EVR

Could you help me I have tried everyway possible, not very good with code. It is giving me a syntax error.

This is the original code

WHERE (((tblTracking.[CRG Deadline])<Now()) AND ((qryrptDistributionDate.Date)>[tblTracking]![CRG Deadline]))

This is the changes I made:

WHERE (((tblTracking.[CRG Deadline])<Now()) AND
Format((qryrptDistributionDate.Date, "Short Date")>
Format[tblTracking]![CRG Deadline], "Short Date"))

Thanks for your help

 

[MarkNie]

Hi PHV

That worked perfect.

Thanks
Alot for your help

 

[IT4EVR]

What is the syntax error you are getting?

 

[IT4EVR]

I think I am going to stop posting responses...lol

Note to self: Just let PHV handle it...he knows all.

From now on I am just going to watch and learn.