Urgent for Jeff Conrad help pls....

G

Guest

Dear Jeff...
I hope u still remember the code that you gave me few weeks back....on
the pop out window.....now i have import the data in it....but i think it
pops out those data eventhough its not 1 week before the expiry date.....
I suspect its the Date format which i import from Excel which i wanted it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....

P.S: btw...if u forget how the code goes...its
Option Compare Database
Option Explicit

12. Now copy and paste ALL of this code right **below** the top two
lines shown above on Step 11:

'**********Code Start**********
Private Sub cmdOpenQuery_Click()
On Error GoTo ErrorPoint

DoCmd.OpenQuery "qryPassportsAboutToExpire"

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorPoint
'************************************
' Name: Form_Open
' Purpose: Check Passport Expirations
'
' Author: Jeff Conrad - Access Junkie
' Date: May 4, 2005
' Comment: For Kelly
'************************************

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCount As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryPassportsAboutToExpire", dbOpenSnapshot)

lngCount = rst.RecordCount

If lngCount > 0 Then
MsgBox "There are Passports about to expire. Please " _
& "review the list to start the paperwork.", vbExclamation _
, "Passport Information"
End If

ExitPoint:
' Cleanup code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
'**********Code End**********
 
J

Jeff Conrad

in message:
Dear Jeff...
I hope u still remember the code that you gave me few weeks back....on
the pop out window.....now i have import the data in it....but i think it
pops out those data eventhough its not 1 week before the expiry date.....
I suspect its the Date format which i import from Excel which i wanted it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....
Click to expand...
P.S: btw...if u forget how the code goes...its
Click to expand...

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
 
G

Guest

Hi Jeff....
I have tried paste the SQL over the existing SQL, but it still run out
the query of passports date like 2006, 2008 and etc which will need a few
years later before expire......
Let me give u a brief information on what i have in my "Passport" table
now so that u have a rough idea...
I import frm Excel my data already ....and the "Expiry Date" format in my
Access table now is DD/MM/YYYY......and the data range is "Text" which i also
have problem in switching the data range to "Date/Time" format...
Do reply asap....

Thanks thanks so much...



Jeff Conrad said:
in message:
Dear Jeff...
I hope u still remember the code that you gave me few weeks back....on
the pop out window.....now i have import the data in it....but i think it
pops out those data eventhough its not 1 week before the expiry date.....
I suspect its the Date format which i import from Excel which i wanted it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....
Click to expand...
P.S: btw...if u forget how the code goes...its
Click to expand...

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
Click to expand...
 
G

Guest

Sorry...
to add in....i had double check which when i run the form again....all the
list of the crew passport exactly from the "passport" table are list
out.......not which i had just mention some as in 2006 - 2008 came out...
mmm...any idea wat happen?
 
J

John Vinson

Sorry...
to add in....i had double check which when i run the form again....all the
list of the crew passport exactly from the "passport" table are list
out.......not which i had just mention some as in 2006 - 2008 came out...
mmm...any idea wat happen?
Click to expand...

It appears that you are storing your date information in a Text field.

The text string "12/31/2048" is - ALPHABETICALLY - before the text
string "2/15/2003" because the character 1 (the first character in the
text string) is lower than the character 2.

If you want to search by dates you *cannot* use a Text datatype, since
Access has no way to know that you intend the value to be interpreted
as a date value.

Import your Excel data into a Date/Time datatype field, not a text! It
may be best to create your Access table, empty, with the proper
datatypes; importing from Excel forces Access to guess what datatype
you want, and it often guesses wrong.

John W. Vinson[MVP]
 
A

adsl

Kelly Lim said:
Dear Jeff...
I hope u still remember the code that you gave me few weeks back....on
the pop out window.....now i have import the data in it....but i think it
pops out those data eventhough its not 1 week before the expiry date.....
I suspect its the Date format which i import from Excel which i wanted
it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....

P.S: btw...if u forget how the code goes...its
Option Compare Database
Option Explicit

12. Now copy and paste ALL of this code right **below** the top two
lines shown above on Step 11:

'**********Code Start**********
Private Sub cmdOpenQuery_Click()
On Error GoTo ErrorPoint

DoCmd.OpenQuery "qryPassportsAboutToExpire"

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorPoint
'************************************
' Name: Form_Open
' Purpose: Check Passport Expirations
'
' Author: Jeff Conrad - Access Junkie
' Date: May 4, 2005
' Comment: For Kelly
'************************************

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCount As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryPassportsAboutToExpire", dbOpenSnapshot)

lngCount = rst.RecordCount

If lngCount > 0 Then
MsgBox "There are Passports about to expire. Please " _
& "review the list to start the paperwork.", vbExclamation _
, "Passport Information"
End If

ExitPoint:
' Cleanup code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
'**********Code End**********
Click to expand...
 
A

adsl

Jeff Conrad said:
in message:
Dear Jeff...
I hope u still remember the code that you gave me few weeks back....on
the pop out window.....now i have import the data in it....but i think it
pops out those data eventhough its not 1 week before the expiry date.....
I suspect its the Date format which i import from Excel which i wanted
it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....
Click to expand...
P.S: btw...if u forget how the code goes...its
Click to expand...

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
Click to expand...
 
A

adsl

Kelly Lim said:
Hi Jeff....
I have tried paste the SQL over the existing SQL, but it still run out
the query of passports date like 2006, 2008 and etc which will need a few
years later before expire......
Let me give u a brief information on what i have in my "Passport" table
now so that u have a rough idea...
I import frm Excel my data already ....and the "Expiry Date" format in
my
Access table now is DD/MM/YYYY......and the data range is "Text" which i
also
have problem in switching the data range to "Date/Time" format...
Do reply asap....

Thanks thanks so much...



Jeff Conrad said:
in message:
Dear Jeff...
I hope u still remember the code that you gave me few weeks
back....on
the pop out window.....now i have import the data in it....but i think
it
pops out those data eventhough its not 1 week before the expiry
date.....
I suspect its the Date format which i import from Excel which i
wanted it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....
Click to expand...
P.S: btw...if u forget how the code goes...its
Click to expand...

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry
Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
Click to expand...
Click to expand...
 
A

adsl

Kelly Lim said:
Sorry...
to add in....i had double check which when i run the form again....all
the
list of the crew passport exactly from the "passport" table are list
out.......not which i had just mention some as in 2006 - 2008 came out...
mmm...any idea wat happen?
Click to expand...
 
A

adsl

adsl said:
Jeff Conrad said:
in message:
Dear Jeff...
I hope u still remember the code that you gave me few weeks
back....on
the pop out window.....now i have import the data in it....but i think
it
pops out those data eventhough its not 1 week before the expiry
date.....
I suspect its the Date format which i import from Excel which i
wanted it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....
Click to expand...
P.S: btw...if u forget how the code goes...its
Click to expand...

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry
Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
Click to expand...
Click to expand...
 
A

adsl

John Vinson said:
It appears that you are storing your date information in a Text field.

The text string "12/31/2048" is - ALPHABETICALLY - before the text
string "2/15/2003" because the character 1 (the first character in the
text string) is lower than the character 2.

If you want to search by dates you *cannot* use a Text datatype, since
Access has no way to know that you intend the value to be interpreted
as a date value.

Import your Excel data into a Date/Time datatype field, not a text! It
may be best to create your Access table, empty, with the proper
datatypes; importing from Excel forces Access to guess what datatype
you want, and it often guesses wrong.

John W. Vinson[MVP]
Click to expand...
 
A

adsl

Kelly Lim said:
Sorry...
to add in....i had double check which when i run the form again....all
the
list of the crew passport exactly from the "passport" table are list
out.......not which i had just mention some as in 2006 - 2008 came out...
mmm...any idea wat happen?
Click to expand...
 
A

adsl

adsl said:
Kelly Lim said:
Hi Jeff....
I have tried paste the SQL over the existing SQL, but it still run out
the query of passports date like 2006, 2008 and etc which will need a few
years later before expire......
Let me give u a brief information on what i have in my "Passport" table
now so that u have a rough idea...
I import frm Excel my data already ....and the "Expiry Date" format in
my
Access table now is DD/MM/YYYY......and the data range is "Text" which i
also
have problem in switching the data range to "Date/Time" format...
Do reply asap....

Thanks thanks so much...



Jeff Conrad said:
in message:

Dear Jeff...
I hope u still remember the code that you gave me few weeks
back....on
the pop out window.....now i have import the data in it....but i think
it
pops out those data eventhough its not 1 week before the expiry
date.....
I suspect its the Date format which i import from Excel which i
wanted it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in
the
format of "Day/Mth/Year???
Urgent pls...thanks again....

P.S: btw...if u forget how the code goes...its

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry
Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
Click to expand...
Click to expand...
Click to expand...
 
A

adsl

adsl said:
Kelly Lim said:
Hi Jeff....
I have tried paste the SQL over the existing SQL, but it still run out
the query of passports date like 2006, 2008 and etc which will need a few
years later before expire......
Let me give u a brief information on what i have in my "Passport" table
now so that u have a rough idea...
I import frm Excel my data already ....and the "Expiry Date" format in
my
Access table now is DD/MM/YYYY......and the data range is "Text" which i
also
have problem in switching the data range to "Date/Time" format...
Do reply asap....

Thanks thanks so much...



Jeff Conrad said:
in message:

Dear Jeff...
I hope u still remember the code that you gave me few weeks
back....on
the pop out window.....now i have import the data in it....but i think
it
pops out those data eventhough its not 1 week before the expiry
date.....
I suspect its the Date format which i import from Excel which i
wanted it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in
the
format of "Day/Mth/Year???
Urgent pls...thanks again....

P.S: btw...if u forget how the code goes...its

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry
Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
Click to expand...
Click to expand...
Click to expand...
 
A

adsl

Kelly Lim said:
Hi Jeff....
I have tried paste the SQL over the existing SQL, but it still run out
the query of passports date like 2006, 2008 and etc which will need a few
years later before expire......
Let me give u a brief information on what i have in my "Passport" table
now so that u have a rough idea...
I import frm Excel my data already ....and the "Expiry Date" format in
my
Access table now is DD/MM/YYYY......and the data range is "Text" which i
also
have problem in switching the data range to "Date/Time" format...
Do reply asap....

Thanks thanks so much...



Jeff Conrad said:
in message:
Dear Jeff...
I hope u still remember the code that you gave me few weeks
back....on
the pop out window.....now i have import the data in it....but i think
it
pops out those data eventhough its not 1 week before the expiry
date.....
I suspect its the Date format which i import from Excel which i
wanted it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....
Click to expand...
P.S: btw...if u forget how the code goes...its
Click to expand...

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry
Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
Click to expand...
Click to expand...
 
A

adsl

Jeff Conrad said:
in message:
Dear Jeff...
I hope u still remember the code that you gave me few weeks back....on
the pop out window.....now i have import the data in it....but i think it
pops out those data eventhough its not 1 week before the expiry date.....
I suspect its the Date format which i import from Excel which i wanted
it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....
Click to expand...
P.S: btw...if u forget how the code goes...its
Click to expand...

<code snipped>

Hi Kelly,

1. Make a backup of your database first.

2. Open the query we created in Design View (qryPassportsAboutToExpire)

3. Switch to SQL view: View | SQL

4. Copy and paste this SQL *over* the existing SQL:

SELECT Passport.*
FROM Passport
WHERE (((Format(([Passport].[Expiry Date]),"\#mm-dd-yyyy\#"))<=Date()+7));

5. Save the query.

Now try running the form and command button query again.
Does that solve the problem?
Click to expand...
 
A

adsl

Kelly Lim said:
Dear Jeff...
I hope u still remember the code that you gave me few weeks back....on
the pop out window.....now i have import the data in it....but i think it
pops out those data eventhough its not 1 week before the expiry date.....
I suspect its the Date format which i import from Excel which i wanted
it
to be Day/Mth/Year and not Mth/Day/Year which Access produce......
Can i know where are the changes for the code in order to work in the
format of "Day/Mth/Year???
Urgent pls...thanks again....

P.S: btw...if u forget how the code goes...its
Option Compare Database
Option Explicit

12. Now copy and paste ALL of this code right **below** the top two
lines shown above on Step 11:

'**********Code Start**********
Private Sub cmdOpenQuery_Click()
On Error GoTo ErrorPoint

DoCmd.OpenQuery "qryPassportsAboutToExpire"

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorPoint
'************************************
' Name: Form_Open
' Purpose: Check Passport Expirations
'
' Author: Jeff Conrad - Access Junkie
' Date: May 4, 2005
' Comment: For Kelly
'************************************

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCount As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryPassportsAboutToExpire", dbOpenSnapshot)

lngCount = rst.RecordCount

If lngCount > 0 Then
MsgBox "There are Passports about to expire. Please " _
& "review the list to start the paperwork.", vbExclamation _
, "Passport Information"
End If

ExitPoint:
' Cleanup code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
'**********Code End**********
Click to expand...
 
J

Jeff Conrad

in message:
Hi Jeff....
I have tried paste the SQL over the existing SQL, but it still run out
the query of passports date like 2006, 2008 and etc which will need a few
years later before expire......
Let me give u a brief information on what i have in my "Passport" table
now so that u have a rough idea...
I import frm Excel my data already ....and the "Expiry Date" format in my
Access table now is DD/MM/YYYY......and the data range is "Text" which i also
have problem in switching the data range to "Date/Time" format...
Do reply asap....

Thanks thanks so much...
Click to expand...

Hi Kelly,

I think John has figured out the cause of the problem.
You need a Date/Time field here.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

mailto: by Dl clicking, how to open Lotus Notes instead of Outlook 2
Open Form based on Field in a hidden form 7
Convert export calendar to Outlook to late binding 0
password Protecting Switchboard Items 7
Startup Password to access switchboard 8
ExitPoint vs. Err_Click 2
Autonumber Restart 7
Subform SQL query syntax & the 3075 error 8

Top