MS Access Query by specific name

[Tobasco1136]

In MS Access 2003, I have 4 test records in tblRecordsCheckedOut. I built a query prompting a user to enter the name of the person checking out a file. When I enter the name, I get a blank record back. If I hit enter (which retrieves all records) I get all 4 records the way it is supposed to work.

Can anyone tell me why my query by a specific person isn't working? I have included the SQL query that Access Built.


SELECT tblRecordsCheckedOut.DateFileCheckedOut, tblRecordsCheckedOut.ProviderFullName, DateDiff("y",[DateFileDue],Now())+30 AS DaysOverdue, tblRecordsCheckedOut.FileCheckedOutTo, tblRecordsCheckedOut.DepartmentAccessingRecord, tblRecordsCheckedOut.DateFileDue
FROM tblRecordsCheckedOut
WHERE (((DateDiff("y",[DateFileDue],Now())+30)>30) AND ((tblRecordsCheckedOut.FileCheckedOutTo) Like "*" & [Enter Name or Hit Enter to Retrieve All Records] & "*"));

 

[VRoscioli]

Are you sure the data matches the other condition in your WHERE clause?


-V

 

[Tobasco1136]

Yes, all 4 records are at least 30 days overdue. And like I said, if I hit enter, all the records are displayed properly.

 

[VRoscioli]

Give us some sample data?


-V

 

[dhookom]

Are you using lookup fields?

IMHO, using parameter prompts is never an appropriate user interface. You should consider using a combo box on a form to select the name.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tobasco1136]

I want the user to be able to type in a name not scroll through a bunch of records. I combo box probably won't work as there are going to be a lot of people checking out the files.

FileCheckedOutTo DepartmentAccessingRecord Reason for Accessing Record DateFileCheckedOut DateFileDue DateFileReturned ProviderFullName
Joe Smith Quality Focus Review 5/25/2007 6/24/2007 Kildear, Stenve
Jane Doe HR Evaluation 7/26/2007 8/25/2007 7/26/2007 Majors, Austin
Kathy Jones Credentials Credentialing 5/26/2007 6/25/2007 Smithe, Alexa
John Bond Quality Quality 7/22/2007 8/21/2007 Anderson, John
Steve Fisher Quality Focus Review 6/15/2007 7/15/2007 Bon Jovi, John
Michael Jordan Quality Quality 6/14/2006 7/14/2006 Miller, Steve

 

[dhookom]

The "combo box" was only a suggestion since it generally is a better interface then typing a value. You could use a text box.

What is the data type of the field FileCheckedOutTo?

Could you answer my question regarding the use of a lookup field?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tobasco1136]

I am just building the query to get the data, then I will build the form.

For the name of the person checking out the file, I am using a lookup field, but I am thinking that isn't the problem as the name is in the table.

FileCheckedOutTo is text with alookup field.

 

[VRoscioli]

And what input(s) are you trying and what outputs are you getting/expecting?


-V

 

[Tobasco1136]

I want the user to type in the name of a person to see what records they have checked out and be able to print a report from that.

 

[Tobasco1136]

I meant to say I want the user to type in a person's name and see if they have any overdue files then print a report.

 

[dhookom]

You might want to read the opinion of most seasoned developers regarding lookup fields

http://www.mvps.org/access/lookupfields.htm.

They often look like they are storing a value that is not true. I would remove all lookup fields and simply use combo boxes on forms for data entry and editing.

If you insist on keeping the lookup field, can you provide the
Row Source:
Bound Column:
Column Widths:
and sample data from the Row Source's table?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tobasco1136]

Ok, put a combo box in that looks a name up in another table and the issue still remains. Any ideas?

 

[dhookom]

Is the field still a lookup? If so, can you answer my previous questions?

Do you see what you expect when you open the debug window (press Ctrl+G) and enter

?DLookup("FileCheckedOutTo","tblRecordsCheckedOut","FileCheckedOutTo Is Not Null")

? Forms!YourFormName!YourComboName

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tobasco1136]

Thanks for your suggestions. I went back and removed the lookup from the table and put it in the report (where it should be) and the query is working.