Solved

What is the appropriate way to check date?

Posted on 2011-09-20
29
229 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 50

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 50

Accepted Solution

by:
Gustav Brock earned 500 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 50

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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