query not going as planned

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

Guest

i am making a gui, and i am using a query to find a record with a
user-inputted ID number.

for example if i use ID number of 4, the search goes as planned, but you
have to scroll through the records before it comes up, as if you were just
scrolling through the records as normal. all the records before it have all
balnk fields, but you still have to scroll through them all until you get to
the record that fills the query's requirements.

is there a way to restrict the query results to only show the records that
fit the bill, and omit the ones that do not?

i am also using this method of queryiung to find last names of those who
inputted the reocrd, so it will not always be a unique record.
 
i am making a gui, and i am using a query to find a record with a
user-inputted ID number.

for example if i use ID number of 4, the search goes as planned, but you
have to scroll through the records before it comes up, as if you were just
scrolling through the records as normal. all the records before it have all
balnk fields, but you still have to scroll through them all until you get to
the record that fills the query's requirements.

is there a way to restrict the query results to only show the records that
fit the bill, and omit the ones that do not?

i am also using this method of queryiung to find last names of those who
inputted the reocrd, so it will not always be a unique record.
Click to expand...

You're doing something wrong with the query then. Could you post the
SQL view of the query, or your code?

Basing a Form on a Query with a criterion on a field will show exactly
and only those records which match that criterion. Blank records
(other than the "new record" at the bottom of the screen) will not be
shown.

John W. Vinson[MVP]
 
this is the SQL view of my query


SELECT records.ID, records.EntryDate, records.ExpiryDate,
records.CaseWorkerFirstName, records.CaseWorkerLastName, records.CaseRemarks
FROM records
WHERE (((records.ID)=[txtID].[value]))
ORDER BY records.ID DESC;
 
this is the SQL view of my query


SELECT records.ID, records.EntryDate, records.ExpiryDate,
records.CaseWorkerFirstName, records.CaseWorkerLastName, records.CaseRemarks
FROM records
WHERE (((records.ID)=[txtID].[value]))
ORDER BY records.ID DESC;
Click to expand...

Try changing the WHERE clause to

WHERE ((([Records].[ID]) = [Forms]![NameOfYourForm]![txtID]))

I don't have ANY idea why what you have should work at all, much less
why it's bringing up blank records!

John W. Vinson[MVP]
 
it's still requiring me to go through the blank records, to get to the
results...
is the fact that it's a subform causing this perhaps?

i realize i may have just nailed the coffin shut forgetting that
information...

John Vinson said:
this is the SQL view of my query


SELECT records.ID, records.EntryDate, records.ExpiryDate,
records.CaseWorkerFirstName, records.CaseWorkerLastName, records.CaseRemarks
FROM records
WHERE (((records.ID)=[txtID].[value]))
ORDER BY records.ID DESC;
Click to expand...

Try changing the WHERE clause to

WHERE ((([Records].[ID]) = [Forms]![NameOfYourForm]![txtID]))

I don't have ANY idea why what you have should work at all, much less
why it's bringing up blank records!

John W. Vinson[MVP]
Click to expand...
 
i decided to transfer everything in the subfrom onto the regular form, and
now it works like a charm! thankyou very much for your assistance!

John Vinson said:
this is the SQL view of my query


SELECT records.ID, records.EntryDate, records.ExpiryDate,
records.CaseWorkerFirstName, records.CaseWorkerLastName, records.CaseRemarks
FROM records
WHERE (((records.ID)=[txtID].[value]))
ORDER BY records.ID DESC;
Click to expand...

Try changing the WHERE clause to

WHERE ((([Records].[ID]) = [Forms]![NameOfYourForm]![txtID]))

I don't have ANY idea why what you have should work at all, much less
why it's bringing up blank records!

John W. Vinson[MVP]
Click to expand...
 
Back
Top