Solved

What is the appropriate way to check date?

Posted on 2011-09-20
29
190 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
 

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 49

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 49

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 49

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now