Query not returning any values, though I know the values to exist

[BlueWolverine]

Hello,
MS ACCESS 2003 on XP Pro

I have a query that looks at a table and finds the records corresponding to
a value stored in a text box on a form.

Here is the SQL
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.VIN)=[Forms]![f_SearchPanel]![GlobalVIN]));


In Datasheet mode, it shows blank, and vba rs.eof shows true. I know for
fact that the value is in fact in the form, and I know for a fact that there
is a record in the table with that VIN. I can go looking for them and see
them.

I have tried '" & [Forms]![f_SearchPanel]![GlobalVIN] & "' as I have found
for otehr vba related calls, but that doesn't appear to work. IN case you
are curious, both fields are text.

Thank you

 

[Jerry Whittle]

The first thing that I would try would be to put in the actual VIN in the
WHERE clause instead of the form reference. Does the query then return the
proper value?

If not it's possible that there is extra leading or trailing spaces in the
form's text box. If that's the case, something below could help:

=Trim([Forms]![f_SearchPanel]![GlobalVIN])));

It's also possible that it doesn't see the value in the form as it's in a
state of 'flux'. In that case the Text property might help. Try this:

=[Forms]![f_SearchPanel]![GlobalVIN].Text));

 

[BlueWolverine]

This time the VBA gave me an error so I altered the SQL for the query to the
following:

SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.VIN)='" & [Forms]![f_SearchPanel]![GlobalVIN] &
"'));


This still does not actually return the value THAT IS THERE!!

Just thought I would add this to my post.

I used the build tool to get the form field, so I am pretty sure that's
spelled correctly.

 

[Allen Browne]

Is this query the RecordSource for f_SearchPanel?

If so, and it is in Datasheet mode, then GlobalVIN is presumably in the
Detail section (that's the only section you can see in Datasheet view), so
it will be referring to the current row. If the current row is the new row,
it won't have any value, so the form won't have any records.

If that's not the case, try these ideas:

1. Make sure you move the focus out of GlobalVIN.
Its Value doesn't get updated while the focus is there.

2. After that requery the form.
You can do that by opening the Immediate Window (Ctrl+G) and entering:
Forms!f_SearchPanel.Requery
This could work around the issue if it's a chicken'n'egg problem (form has
to run the query before the text boxes can get a value, and text box has to
have a value before it can supply one to the query.)

3. Try a compact/repair.
If VIN is indexed, this could fix a problem with the index.

 

[BlueWolverine]

Here's What I have tried.

..text did not appear to work

There aren't leading spaces.

the query is the recordset for some code ONLY. There is NO recordset for
the entirety of the search panel. It has a lot of combo boxes and buttons
that find recordset based forms, based on the contents of the combo boxes.
the globalvin was (an admittedly amateur) workaround to having 4000 forms and
queries.

VIN is an index, but in a completely different table. The index in the
important table is the combination of FuelCardNo and FuelCardProvider. I
don't care about the particular card, I just need to know if table has a
record for the VIN i'm worried about with the Provider I'm worried about.

Copying and pasting the VIN into the query WORKS 100% proving my data is
there.

I have left the f_searchpanel clicked on a random non-vin field and that
didn't work.

everything is in datasheet or non-design mode for all of this.

requery didn't work.

I have updated the sql to be more intelligent and to avoid looping but it
still doesn't work.

SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] & "'));

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Is this query the RecordSource for f_SearchPanel?

If so, and it is in Datasheet mode, then GlobalVIN is presumably in the
Detail section (that's the only section you can see in Datasheet view), so
it will be referring to the current row. If the current row is the new row,
it won't have any value, so the form won't have any records.

If that's not the case, try these ideas:

1. Make sure you move the focus out of GlobalVIN.
Its Value doesn't get updated while the focus is there.

2. After that requery the form.
You can do that by opening the Immediate Window (Ctrl+G) and entering:
Forms!f_SearchPanel.Requery
This could work around the issue if it's a chicken'n'egg problem (form has
to run the query before the text boxes can get a value, and text box has to
have a value before it can supply one to the query.)

3. Try a compact/repair.
If VIN is indexed, this could fix a problem with the index.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,
MS ACCESS 2003 on XP Pro

I have a query that looks at a table and finds the records corresponding
to
a value stored in a text box on a form.

Here is the SQL
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.VIN)=[Forms]![f_SearchPanel]![GlobalVIN]));


In Datasheet mode, it shows blank, and vba rs.eof shows true. I know for
fact that the value is in fact in the form, and I know for a fact that
there
is a record in the table with that VIN. I can go looking for them and see
them.

I have tried '" & [Forms]![f_SearchPanel]![GlobalVIN] & "' as I have found
for otehr vba related calls, but that doesn't appear to work. IN case you
are curious, both fields are text.

 

[BlueWolverine]

REPOST DUE TO LACK OF ANSWERS

Here's What I have tried.

..text did not appear to work

There aren't leading spaces.

the query is the recordset for some code ONLY. There is NO recordset for
the entirety of the search panel. It has a lot of combo boxes and buttons
that find recordset based forms, based on the contents of the combo boxes.
the globalvin was (an admittedly amateur) workaround to having 4000 forms and
queries.

VIN is an index, but in a completely different table. The index in the
important table is the combination of FuelCardNo and FuelCardProvider. I
don't care about the particular card, I just need to know if table has a
record for the VIN i'm worried about with the Provider I'm worried about.

Copying and pasting the VIN into the query WORKS 100% proving my data is
there.

I have left the f_searchpanel clicked on a random non-vin field and that
didn't work.

everything is in datasheet or non-design mode for all of this.

requery didn't work.

I have updated the sql to be more intelligent and to avoid looping but it
still doesn't work.

SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] & "'));

 

[Allen Browne]

Okay, this is for a Recordset you opened in code, so try something like this
to debug it:

Dim rs As DAO.Recordset
Dim strSql As String

Debug.Print "VIN is " & Forms!f_SearchPanel!GlobalVIN
strSql = "SELECT FuelCardProvider, VIN FROM t_FuelCardInventory " & _
"WHERE VIN = " & """ & Forms!f_SearchPanel!GlobalVIN & """;"
Debug.Print strSql
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Debug.Print rs.RecordCount
Stop

rs.Close
Set rs = Nothing

Do not include any error handing: you want to know if there are errors.
Also, in the code window, go to Tools | Options | General, and make sure
Error Trapping is set to:
Break on Unhandled Errors

Verify that the results are as you expect.

Note that the RecordCount should be 1 if there are any records, or 0 if
none. (The actual count won't be available immediately after opening the
rs.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Here's What I have tried.

.text did not appear to work

There aren't leading spaces.

the query is the recordset for some code ONLY. There is NO recordset for
the entirety of the search panel. It has a lot of combo boxes and buttons
that find recordset based forms, based on the contents of the combo boxes.
the globalvin was (an admittedly amateur) workaround to having 4000 forms
and
queries.

VIN is an index, but in a completely different table. The index in the
important table is the combination of FuelCardNo and FuelCardProvider. I
don't care about the particular card, I just need to know if table has a
record for the VIN i'm worried about with the Provider I'm worried about.

Copying and pasting the VIN into the query WORKS 100% proving my data is
there.

I have left the f_searchpanel clicked on a random non-vin field and that
didn't work.

everything is in datasheet or non-design mode for all of this.

requery didn't work.

I have updated the sql to be more intelligent and to avoid looping but it
still doesn't work.

SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] & "'));

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Is this query the RecordSource for f_SearchPanel?

If so, and it is in Datasheet mode, then GlobalVIN is presumably in the
Detail section (that's the only section you can see in Datasheet view),
so
it will be referring to the current row. If the current row is the new
row,
it won't have any value, so the form won't have any records.

If that's not the case, try these ideas:

1. Make sure you move the focus out of GlobalVIN.
Its Value doesn't get updated while the focus is there.

2. After that requery the form.
You can do that by opening the Immediate Window (Ctrl+G) and entering:
Forms!f_SearchPanel.Requery
This could work around the issue if it's a chicken'n'egg problem (form
has
to run the query before the text boxes can get a value, and text box has
to
have a value before it can supply one to the query.)

3. Try a compact/repair.
If VIN is indexed, this could fix a problem with the index.

message

Hello,
MS ACCESS 2003 on XP Pro

I have a query that looks at a table and finds the records
corresponding
to
a value stored in a text box on a form.

Here is the SQL
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE
(((t_FuelCardInventory.VIN)=[Forms]![f_SearchPanel]![GlobalVIN]));


In Datasheet mode, it shows blank, and vba rs.eof shows true. I know
for
fact that the value is in fact in the form, and I know for a fact that
there
is a record in the table with that VIN. I can go looking for them and
see
them.

I have tried '" & [Forms]![f_SearchPanel]![GlobalVIN] & "' as I have
found
for otehr vba related calls, but that doesn't appear to work. IN case
you
are curious, both fields are text.