Solved

What is the appropriate way to check date?

Posted on 2011-09-20
29
223 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

756 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