?
Solved

What is the appropriate way to check date?

Posted on 2011-09-20
29
Medium Priority
?
243 Views
Last Modified: 2012-06-27
Hi:
I always get confuse with dates.
I have combo box to select a specific date of fingerprint log file
"SELECT DISTINCT tblFingerprintData_log.InOutHDate, tblFingerprintData_log.InOutGDate FROM tblFingerprintData_log ORDER BY [InOutGDate] DESC;"

If the specific date is already exist in table "tblAttendance", rise an err message
MsgBox "Fingerprint Data of this day is already exist!", vbExclamation
Please help
There is  an attach mdb file

ask-about-date.mdb
0
Comment
Question by:Mohammad Alsolaiman
  • 10
  • 9
  • 7
  • +1
29 Comments
 
LVL 75
ID: 36569093
How about this:

Private Sub cboFingerPrintDate_BeforeUpdate(Cancel As Integer)

   
    If Nz(DLookup("[AttendanceHijriDate]", "tblAttendance"), "") > "" Then
        MsgBox "Fingerprint Data of this day is already exist!", vbExclamation
        Cancel = True
    End If
End Sub

mx
0
 
LVL 75
ID: 36569108
oops ignore that ...
0
 
LVL 75
ID: 36569128
Try this:

Private Sub cboFingerPrintDate_BeforeUpdate(Cancel As Integer)

   
    If Nz(DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = #" & Me.cboFingerPrintDate & "#"), "") > "" Then
        MsgBox "Fingerprint Data of this day is already exist!", vbExclamation
        Cancel = True
    End If
   
   
End Sub
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Mohammad Alsolaiman
ID: 36569228
Dose it work with you?
I try this
MsgBox Nz(DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = #" & Me.cboFingerPrintDate & "#"), "")
And I get blank!
0
 
LVL 75
ID: 36569263
Yes - see image.

I had to change a couple of AttendanceHijriDate  dates to test ...

mx
Capture1.gif
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36569334
When I open your stuff, my results may not match yours because of the regional calendar setting differences.
We've worked out in the past how to massage Gregorian Dates to Hijiri and back.
Can you explain what the problem is, and how to use your sample?
It throws errors when I click the button
0
 
LVL 75
ID: 36569369
"It throws errors when I click the button"

Yeah ... the automation error.

I just created a new form with just the combo to test with ... and that works.

mx
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 36569483
i change the format property of the combo box to short date , but nothing
i try in the code , Cdate(...) but still nothing?!!
0
 
LVL 75
ID: 36569491
ok... We are concerned with this date right ... AttendanceHijriDate  ?

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36569546
<format property of the combo box>
What the first question about dates I answered for you taught me is to have great respect for the intracacies of dates!
The FORMAT or how you see the date has absolutely nothing to do with how it is stored
So I ask again <Can you explain what the problem is> ?

There is problems with your syntax.
If DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = & CDate(Me.cboFingerPrintDate)) Then
DLookup returns a value not a boolean
So this syntax is missing something
If DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = & CDate(Me.cboFingerPrintDate)) = SomethingYouHaveNotPutInYet Then

Is this the problem?
0
 
LVL 75
ID: 36569562
This works:

    If Nz(DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = #" & Me.cboFingerPrintDate & "#"), "") > "" Then

which is what I posted and tested.

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36569641
@mx
Hashing always gets dicey with non-US regional settings, which this OP has.
The date functions (Date, CDate, DateValue, etc) are regional calendar and Hijiri aware so sometimes you need to take your string control values and run them through a function to make sure you are getting what you expect.

@M_SOLAIMAN
You take the time to declare and set
Dim dtAttendanceGregorianDate As String

dtAttendanceGregorianDate = Nz(DLookup("[AttendanceGregorianDate]", "tblAttendance", "[AttendanceGregorianDate] = " _
    & "#" & Trim(Me.cboFingerPrintDate.Column(1)) & "#"))


But then the rest of your code doesn't use it.

Your table already contains the Hijiri and Gregorian dates

What about

dim rs as recordset
set rs = currentdb.openrecordset("Select * from tblFingerprintData_log where InOutHDate = #" & CDate(Me.cboFingerPrintDate.value) & "#;",dbopendynaset)
if rs.recordcount <> 0 then
    MsgBox "Fingerprint Data of this day is already exist!", vbExclamation
end if
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36571789
Values from combos are always text. Thus a date expression has to be converted as to the local settings.
To use that value in DLookup, it must be formatted as text in either ISO or the "reverse" US format.
DLookup returns Null when nothing is found:

If Not IsNull(DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = #" & Format(DateValue(Me.cboFingerPrintDate), "yyyy\/mm\/dd") & "#")) Then

/gustav
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 36574793
DatabaseMX:
    If Nz(DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = #" & Me.cboFingerPrintDate & "#"), "") > "" Then
Nick67:
dim rs as recordset
set rs = currentdb.openrecordset("Select * from tblFingerprintData_log where InOutHDate = #" & CDate(Me.cboFingerPrintDate.value) & "#;",dbopendynaset)
if rs.recordcount <> 0 then
    MsgBox "Fingerprint Data of this day is already exist!", vbExclamation
end if
cactus_data:
If Not IsNull(DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = #" & Format(DateValue(Me.cboFingerPrintDate), "yyyy\/mm\/dd") & "#")) Then
i try all of the above
but still nothing

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36574806
Can you post a working sample
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 36574833
cactus_data:
Values from combos are always text. Thus a date expression has to be converted as to the local settings.
i do the obesity
i convert  "Cstr([AttendanceHijriDate]) = '" to text then mach between them and it's work good
is this god logic to work with?


Private Sub btnFingerprint2_Click()
On Error GoTo Err_btnFingerprint2_Click

 If Nz(DLookup("[AttendanceHijriDate]", "tblAttendance", "Cstr([AttendanceHijriDate]) = '" & Me.cboFingerPrintDate & "'"), "") > "" Then

   MsgBox "Fingerprint Data of this day is already exist!", vbExclamation
Else
    MsgBox "Here"
End If
Exit_btnFingerprint2_Click:
    Exit Sub

Err_btnFingerprint2_Click:
    MsgBox err.Number & "   " & err.Description & "btnFingerprint2_Click"
    Resume Exit_btnFingerprint2_Click
   
End Sub
remember, it is work good this way!
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 36574904
> .. convert  "Cstr([AttendanceHijriDate]) = '" to text then mach between them

Yes, that may work but I cannot test it here:

If Not IsNull(DLookup("[AttendanceHijriDate]", "tblAttendance", "CStr([AttendanceHijriDate]) = '" & Me.cboFingerPrintDate & "'")) Then

/gustav
0
 

Author Closing Comment

by:Mohammad Alsolaiman
ID: 36580468
thanks very much for all that try to help
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 36580472
Actually I don’t know what to say in this case
Each time I come to face a date matter(Hijri Date matter) in ms access, I had a stomachache. Because I know it will take at least 4 days to solve. And the solution may be a right solution, or sometimes a Circumvent solution (like IN our case here).
But maybe  someday I 'll reach it.
I know that all of your solutions are correct by your side (Gregorian date, with English devices) , but still wondering where is the problem with dealing with Arabic regional settings.
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 36580486
Oops
Nick67:
Can you post a working sample
very sorry, didn't notice
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36580537
You are welcome. However, I cannot advise further, as the only situations I meet Hijri dates are in this forum.

/gustav
0
 
LVL 75
ID: 36582777
M_SOLAIMAN:

btw ... how is your application doing overall ?  I recall it was pretty cool.

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36582801
Ok,
I have a toy here for you to play with.
It helps to understand that Access stores datetimes as a double precision number.
Day zero is 1899-12-30.
It is a special day, because any TIME value stored gets this date.
Conversions to Hijiri and back on that date fail.
All others work.

I hope that the toy will help with your stomach ache.
The principles are clear.
If you are dealing with a string Hijiri date value, put this line in before you begin to manipulate that string
Calendar = vbCalHijri
Coerce the string to a DateTime with CDate, and then format your result

If you are dealing with a string Gregorian date value, put this line in before you begin to manipulate that string
Calendar = vbCalGreg
Coerce the string to a DateTime with CDate, and then format your result

You must be rigorous in this regard.
But no matter what you do with formats or calendars, Access stores dates in the same fashion for us all.

Dates.mdb
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 36587133
DatabaseMX:
btw ... how is your application doing overall ?  I recall it was pretty cool.
Yes, doing will with text comparison , but I was wondering if it's logical to do so!


Nick67:
If you are dealing with a string Hijiri date value, put this line in before you begin to manipulate that string
Calendar = vbCalHijri
Thanks very much Nick67, and I'll take my time to study your helpful toy. And will return back to you when understand it.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36587847
<Yes, doing will with text comparison , but I was wondering if it's logical to do so>
If it is robust and it works, then yes, it is a good way to do so.
Since the comparison of [Hijiri Date] = CDate[combo box value]  did not want to work,
the comparison of Cstr([Hijiri Date]) = [Combo box value] was a good substitute.

IF your combo box was filled, or could be filled, with formats unlike those in your table, you could be in trouble.
But you seem to have that well in hand, so your approach should be good
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 36588143
Thank you very much, I trust you genius guys
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36588164
Trust, but verify :)
0
 
LVL 75
ID: 36588194
LOL .... ooooh yeah.

mx
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 36588212
Much business , less verify!.  but I think that I start to organize my self. And this will give me more enough time for verification.  
thanks Nick67
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question