• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

What is the appropriate way to check date?

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
Mohammad Alsolaiman
Asked:
Mohammad Alsolaiman
  • 10
  • 9
  • 7
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
oops ignore that ...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Mohammad Alsolaimanapplication programmerAuthor Commented:
Dose it work with you?
I try this
MsgBox Nz(DLookup("[AttendanceHijriDate]", "tblAttendance", "[AttendanceHijriDate] = #" & Me.cboFingerPrintDate & "#"), "")
And I get blank!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes - see image.

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

mx
Capture1.gif
0
 
Nick67Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
i change the format property of the combo box to short date , but nothing
i try in the code , Cdate(...) but still nothing?!!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok... We are concerned with this date right ... AttendanceHijriDate  ?

mx
0
 
Nick67Commented:
<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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This works:

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

which is what I posted and tested.

mx
0
 
Nick67Commented:
@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
 
Gustav BrockCIOCommented:
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
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
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
 
Nick67Commented:
Can you post a working sample
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
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
 
Gustav BrockCIOCommented:
> .. 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
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
thanks very much for all that try to help
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
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
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
Oops
Nick67:
Can you post a working sample
very sorry, didn't notice
0
 
Gustav BrockCIOCommented:
You are welcome. However, I cannot advise further, as the only situations I meet Hijri dates are in this forum.

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
M_SOLAIMAN:

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

mx
0
 
Nick67Commented:
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
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
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
 
Nick67Commented:
<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
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
Thank you very much, I trust you genius guys
0
 
Nick67Commented:
Trust, but verify :)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
LOL .... ooooh yeah.

mx
0
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 9
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now