FindFirst Problem

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

You are heading for a nasty "type conversion" error in your date processing.
I don't have Access here to check, but I believe that in your code, it will
convert the date field value to a >string<, and compare that string,
verbatim, to whatever you typed in the textbox. So, for example, "1/1/2003"
would >not< match "1-Jan-2003", even though they are the same date. The code
that you have written there is definitely not very sound.

Instead, use this:

pts.FindFirst "[SDate]=#" & format$(Me![SDate], "mm/dd/yyyy") & "# AND
[STime]=" & me![STime]

This converts the date to a proper date value using the #...# mehod. That
method requires the date to be in US (m/d/y) format, regardless of the date
settings on your PC.

A similar issue might occur with the time comparison, depending on the data
type of the table field, & what you plan to enter into the time textbox. You
have to think this out carefully. Access will do what you SAY - not
necessarly what you EXPECT!

HTH,
TC
 
No probs :-)

But remember: even if the date test "did work", that would only have been by
chance. It might not have worked if you ran it on a PC with other control
panel date settings. It is never safe to compare a date field and a text
field directly.

Cheers,
TC


Andrew said:
I have it working like I want now, I did have to format
the time, just to let you know Format(Me![STime], "hh:mm
AMPM"}

Thanks for you help
-----Original Message-----
You are heading for a nasty "type conversion" error in your date processing.
I don't have Access here to check, but I believe that in your code, it will
convert the date field value to a >string<, and compare that string,
verbatim, to whatever you typed in the textbox. So, for example, "1/1/2003"
would >not< match "1-Jan-2003", even though they are the same date. The code
that you have written there is definitely not very sound.

Instead, use this:

pts.FindFirst "[SDate]=#" & format$(Me!
Click to expand...
[SDate], "mm/dd/yyyy") & "# AND
[STime]=" & me![STime]

This converts the date to a proper date value using the #...# mehod. That
method requires the date to be in US (m/d/y) format, regardless of the date
settings on your PC.

A similar issue might occur with the time comparison, depending on the data
type of the table field, & what you plan to enter into the time textbox. You
have to think this out carefully. Access will do what you SAY - not
necessarly what you EXPECT!

HTH,
TC


What I want to do it I want to find a record in the same
table, but using to fields. I have a [SDate] and a
[STime] Text boxes.I want to us this code:

pts.FindFirst "[SDate]=" & Me![SDate]

But I don't know how to add the second Text box were it
can look for.

Basicly I want it to find the first record with the two
matching fields.
Click to expand...


.
Click to expand...
Click to expand...
 
What I want to do it I want to find a record in the same
table, but using to fields. I have a [SDate] and a
[STime] Text boxes.I want to us this code:

pts.FindFirst "[SDate]=" & Me![SDate]

But I don't know how to add the second Text box were it
can look for.

Basicly I want it to find the first record with the two
matching fields.
 
Do I need to format the time as well. I have it as "
01:30 PM "
or will it work as is.. and the Date format did work, I
was missing the # and that is why I could not get it to
work, but let me know about the time if you can..



-----Original Message

-----
You are heading for a nasty "type conversion" error in your date processing.
I don't have Access here to check, but I believe that in your code, it will
convert the date field value to a >string<, and compare that string,
verbatim, to whatever you typed in the textbox. So, for example, "1/1/2003"
would >not< match "1-Jan-2003", even though they are the same date. The code
that you have written there is definitely not very sound.

Instead, use this:

pts.FindFirst "[SDate]=#" & format$(Me!
Click to expand...
[SDate], "mm/dd/yyyy") & "# AND
[STime]=" & me![STime]

This converts the date to a proper date value using the #...# mehod. That
method requires the date to be in US (m/d/y) format, regardless of the date
settings on your PC.

A similar issue might occur with the time comparison, depending on the data
type of the table field, & what you plan to enter into the time textbox. You
have to think this out carefully. Access will do what you SAY - not
necessarly what you EXPECT!

HTH,
TC


What I want to do it I want to find a record in the same
table, but using to fields. I have a [SDate] and a
[STime] Text boxes.I want to us this code:

pts.FindFirst "[SDate]=" & Me![SDate]

But I don't know how to add the second Text box were it
can look for.

Basicly I want it to find the first record with the two
matching fields.
Click to expand...


.
Click to expand...
 
I have it working like I want now, I did have to format
the time, just to let you know Format(Me![STime], "hh:mm
AMPM"}

Thanks for you help
-----Original Message-----
You are heading for a nasty "type conversion" error in your date processing.
I don't have Access here to check, but I believe that in your code, it will
convert the date field value to a >string<, and compare that string,
verbatim, to whatever you typed in the textbox. So, for example, "1/1/2003"
would >not< match "1-Jan-2003", even though they are the same date. The code
that you have written there is definitely not very sound.

Instead, use this:

pts.FindFirst "[SDate]=#" & format$(Me!
Click to expand...
[SDate], "mm/dd/yyyy") & "# AND
[STime]=" & me![STime]

This converts the date to a proper date value using the #...# mehod. That
method requires the date to be in US (m/d/y) format, regardless of the date
settings on your PC.

A similar issue might occur with the time comparison, depending on the data
type of the table field, & what you plan to enter into the time textbox. You
have to think this out carefully. Access will do what you SAY - not
necessarly what you EXPECT!

HTH,
TC


What I want to do it I want to find a record in the same
table, but using to fields. I have a [SDate] and a
[STime] Text boxes.I want to us this code:

pts.FindFirst "[SDate]=" & Me![SDate]

But I don't know how to add the second Text box were it
can look for.

Basicly I want it to find the first record with the two
matching fields.
Click to expand...


.
Click to expand...
 
Back
Top