query a time text field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Writing a QBF but having problem searching a time field. It was created as a
text field, so I wrote my criteria as a text field, see below.
If Not IsNull(txtTimeserved) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[time served] = '" & txtTimeserved & "'"
End If
Returns nothing. Do I need to convert to date/time and then format it like
I have done with date fields?
 
Not all text fields are Null when blank. Some are Zero Length Strings.(ZLS).
So, your trap with IsNull may fail without you knowing.

I prefer: Len(nz(fieldname,0)) = 0
 
alavaz17 said:
Writing a QBF but having problem searching a time field. It was created as a
text field, so I wrote my criteria as a text field, see below.
If Not IsNull(txtTimeserved) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[time served] = '" & txtTimeserved & "'"
End If
Returns nothing. Do I need to convert to date/time and then format it like
I have done with date fields?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This depends on the data type of the column [time served]. What is it's
data type in the table?

If it is a number then use

Whereclause = Whereclause & "[time served]=" & txtTimeserved

If it is a Text column then use

Whereclause = Whereclause & "[time served]='" & txtTimeserved & "'"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9FKpoechKqOuFEgEQJ74wCfZwAzEsBeYK78GCfEVlQWnTZaH58An113
3oDUvQD3fUS+4qMw1jlOOW1U
=Uuk/
-----END PGP SIGNATURE-----
 
Okay I tried to follow your idea:

If Len(Nz(txtTimeserved,0))=0 Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[time served] = '" & txtTimeserved & "'"
End If

Now it doesn't even search, it returns all records. Did I mess up?
Thanks

[MVP] S.Clark said:
Not all text fields are Null when blank. Some are Zero Length Strings.(ZLS).
So, your trap with IsNull may fail without you knowing.

I prefer: Len(nz(fieldname,0)) = 0


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

alavaz17 said:
Writing a QBF but having problem searching a time field. It was created
as a
text field, so I wrote my criteria as a text field, see below.
If Not IsNull(txtTimeserved) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[time served] = '" & txtTimeserved & "'"
End If
Returns nothing. Do I need to convert to date/time and then format it
like
I have done with date fields?
Click to expand...
Click to expand...
 
It is a text field and as far as I could tell, my code matches exactly what
you show.
Thanks

MGFoster said:
alavaz17 said:
Writing a QBF but having problem searching a time field. It was created as a
text field, so I wrote my criteria as a text field, see below.
If Not IsNull(txtTimeserved) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[time served] = '" & txtTimeserved & "'"
End If
Returns nothing. Do I need to convert to date/time and then format it like
I have done with date fields?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This depends on the data type of the column [time served]. What is it's
data type in the table?

If it is a number then use

Whereclause = Whereclause & "[time served]=" & txtTimeserved

If it is a Text column then use

Whereclause = Whereclause & "[time served]='" & txtTimeserved & "'"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9FKpoechKqOuFEgEQJ74wCfZwAzEsBeYK78GCfEVlQWnTZaH58An113
3oDUvQD3fUS+4qMw1jlOOW1U
=Uuk/
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top