473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date comparison statement not reading true

MMcCarthy
14,534 Expert Mod 8TB
Hi guys,

I know I must be missing something obvious here. Can anyone spot why the if statement is not reading true.

Expand|Select|Wrap|Line Numbers
  1. Function checkCummLeave(dateCheck As Date, dayAmt As Integer)
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim idNum As Integer
  5.  
  6.     Set db = CurrentDb
  7.     Debug.Print Format(dateCheck, "dd mmmm yyyy") ' immediate window shows 06 February 2007
  8.     Debug.Print Format(Forms!Employees!txtRangeFrom, "dd mmmm yyyy") ' immediate window shows 01 April 2007
  9.  
  10.     If Format(dateCheck, "dd mmmm yyyy") < Format(Forms!Employees!txtRangeFrom, "dd mmmm yyyy") Then
  11.         Set rs = db.OpenRecordset("SELECT * FROM CumulativeLeave WHERE PersonnelNo=" & Me.PersonelNo & _
  12.             " ORDER BY [Year Beginning] DESC")
  13.  
It's not entering the IF statement just going straight to End If

Mary
Oct 26 '07 #1
5 1663
MMcCarthy
14,534 Expert Mod 8TB
Sorry guys,

I'm an idiot at 3:00 am. I was of course comparing strings not dates. Sorry this is a complicated database involving a lot of dates and Access propensity to format everything as mm/dd/yyyy has been driving me slowly insane.

Mary
Oct 26 '07 #2
ADezii
8,834 Expert 8TB
Hi guys,

I know I must be missing something obvious here. Can anyone spot why the if statement is not reading true.

Expand|Select|Wrap|Line Numbers
  1. Function checkCummLeave(dateCheck As Date, dayAmt As Integer)
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim idNum As Integer
  5.  
  6.     Set db = CurrentDb
  7.     Debug.Print Format(dateCheck, "dd mmmm yyyy") ' immediate window shows 06 February 2007
  8.     Debug.Print Format(Forms!Employees!txtRangeFrom, "dd mmmm yyyy") ' immediate window shows 01 April 2007
  9.  
  10.     If Format(dateCheck, "dd mmmm yyyy") < Format(Forms!Employees!txtRangeFrom, "dd mmmm yyyy") Then
  11.         Set rs = db.OpenRecordset("SELECT * FROM CumulativeLeave WHERE PersonnelNo=" & Me.PersonelNo & _
  12.             " ORDER BY [Year Beginning] DESC")
  13.  
It's not entering the IF statement just going straight to End If

Mary
The Format Function returns a Variant of SubType String, which means that you are comparing Strings and not actual Dates. Change the comparison to read:
Expand|Select|Wrap|Line Numbers
  1. If CDate(Me![dateCheck])  < CDate(Forms!Employees![txtRangeFrom]) 
'I explicitly converted the values to Dates, because I never like to make the assumption that they actually are, and if they aren't, a Run Time Error will be generated instead of just simply falling through.
Oct 26 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
The Format Function returns a Variant of SubType String, which means that you are comparing Strings and not actual Dates. Change the comparison to read:
Expand|Select|Wrap|Line Numbers
  1. If CDate(Me![dateCheck])  < CDate(Forms!Employees![txtRangeFrom]) 
'I explicitly converted the values to Dates, because I never like to make the assumption that they actually are, and if they aren't, a Run Time Error will be generated instead of just simply falling through.
Thanks ADezii, thats how I solved it. The whole issue of dates is driving me mad with this database though. For some reason only Long Date formats are reading correctly for me and even if I format a local date variable when assigning a value to it I still have to format it each time I use it.

If you know of any way around this problem I'd love to hear it.

Mary
Oct 26 '07 #4
ADezii
8,834 Expert 8TB
Thanks ADezii, thats how I solved it. The whole issue of dates is driving me mad with this database though. For some reason only Long Date formats are reading correctly for me and even if I format a local date variable when assigning a value to it I still have to format it each time I use it.

If you know of any way around this problem I'd love to hear it.

Mary
This is a rather unique problem that I have never come across. Instead of formatting each Variable occurrence, why not simply pass the Date Variable to a Public Function that accepts the Date as an Argument, properly formats it, then returns the formatted Date? This way you will be guaranteed that all Date Values are in an acceptable and consistent Format.
Oct 26 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
This is a rather unique problem that I have never come across. Instead of formatting each Variable occurrence, why not simply pass the Date Variable to a Public Function that accepts the Date as an Argument, properly formats it, then returns the formatted Date? This way you will be guaranteed that all Date Values are in an acceptable and consistent Format.
Not a bad idea. Worth trying anyway.

I've finished this particular project and don't have the time to go back over and change it but I'll definitely keep it in mind for the future.

Mary
Oct 26 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Lloyd Sheen | last post by:
Below is the output from command window. This shows values of variables and result of comparison. Make no sense except to dot.net. Any ideas?? ?pofile #11/11/2003 12:39:22 PM# ?pdtdbdate
8
by: Lloyd Sheen | last post by:
Below is the output from command window. This shows values of variables and result of comparison. Make no sense except to dot.net. Any ideas?? ?pofile #11/11/2003 12:39:22 PM# ?pdtdbdate
0
by: jenkinsdjj | last post by:
why does this sql statement continually produce a syntax error (missing operator)? ("SELECT * FROM WHERE " & ActualDueDt & " <= " & ScheduleDate) & _ " AND " & DeleteInd & " = 'N'" & _ ...
4
by: anagai | last post by:
I just want to check if a date entered in a textbox is equal to the current system date. I set the date object from the input field like this: dt1=new Date('10/01/2007'); the current system...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.