Hi guys,
I know I must be missing something obvious here. Can anyone spot why the if statement is not reading true. -
Function checkCummLeave(dateCheck As Date, dayAmt As Integer)
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim idNum As Integer
-
-
Set db = CurrentDb
-
Debug.Print Format(dateCheck, "dd mmmm yyyy") ' immediate window shows 06 February 2007
-
Debug.Print Format(Forms!Employees!txtRangeFrom, "dd mmmm yyyy") ' immediate window shows 01 April 2007
-
-
If Format(dateCheck, "dd mmmm yyyy") < Format(Forms!Employees!txtRangeFrom, "dd mmmm yyyy") Then
-
Set rs = db.OpenRecordset("SELECT * FROM CumulativeLeave WHERE PersonnelNo=" & Me.PersonelNo & _
-
" ORDER BY [Year Beginning] DESC")
-
It's not entering the IF statement just going straight to End If
Mary
5 1663
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
Hi guys,
I know I must be missing something obvious here. Can anyone spot why the if statement is not reading true. -
Function checkCummLeave(dateCheck As Date, dayAmt As Integer)
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim idNum As Integer
-
-
Set db = CurrentDb
-
Debug.Print Format(dateCheck, "dd mmmm yyyy") ' immediate window shows 06 February 2007
-
Debug.Print Format(Forms!Employees!txtRangeFrom, "dd mmmm yyyy") ' immediate window shows 01 April 2007
-
-
If Format(dateCheck, "dd mmmm yyyy") < Format(Forms!Employees!txtRangeFrom, "dd mmmm yyyy") Then
-
Set rs = db.OpenRecordset("SELECT * FROM CumulativeLeave WHERE PersonnelNo=" & Me.PersonelNo & _
-
" ORDER BY [Year Beginning] DESC")
-
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: - 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.
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: - 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
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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
|
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
|
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'" & _
...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |