Query to generate form data.

[Knildon]

I have been fighting this for a couple of days and could use some help.
I have a program that keeps track of contacts made with people and need to know the last time (most recent date) that I contacted an individual and display the info. Here is what I have so far:
'Dim LLSQL As String
'Dim LLSearchString As String
'LLSearchString = txtSearchString 'txtSearchString and Contact are the same.
'LLSQL = "SELECT * FROM ContactsTBL WHERE Contact = '" & LLSearchString & "'"
'Form_LookUpPriorContactFRM.RecordSource = LLSQL
I then transfer this data from the LookUpPriorContactFRM form to the screen form for display. The problem is that I always get the oldest date and not the most recent. I've tried to add a second parameter to the SELECT statement but can't come up with the right combination to get it to work. I've tried DMAX(ContactDate,Table) and MAX(ContactDate) with no luck. Is it possible that RecordSource will not allow more than one parameter? If this ever works right it would only give me one record with the most recent date.
Can anyone help me with this problem?
Thanks

 

[pbaldy]

Try

SELECT TOP 1 ...
FROM...
ORDER BY ContactDate DESC

 

[Knildon]

Paul,
Thanks for the quick reply. I should have put in my post that I am new at Access and self taught and don't understand a lot of the functions. I had a poor teacher!!!
Your answer makes sense to put the info in descending order but I don't know how to incorporate the code into the code that I have. I was trying to use an AND statement with my attempts but I don't think that will work. If you could give me an example it would help tremendously.
Thanks again for your help.
Don

 

[Knildon]

I found the solution to the problem.
I added a line to the code and now I get the most recent contact. For some unknown reason the code did not like everything in one statement?? I forgot to mention that I'm using Access 2000 if that matters.
Dim LLSQL As String
Dim LLSearchString As String
LLSearchString = txtSearchString 'txtSearchString and Contact are the same.
LLSQL = "SELECT * FROM ContactsTBL WHERE Contact = '" & LLSearchString & "'"
The following line was added:
LLSQL = LLSQL & " ORDER BY ContactDate DESC"
Form_LookUpPriorContactFRM.RecordSource = LLSQL
Thanks to Paul for his suggestion.
Consider this thread closed.

 

[pbaldy]

It doesn't care if it's on one line so long as the syntax remains correct. It would look like:

LLSQL = "SELECT * FROM ContactsTBL WHERE Contact = '" & LLSearchString & "' ORDER BY ContactDate DESC"

I would also have used the TOP predicate, as what you have could return multiple records.

 

[Knildon]

Hi Paul,
Thanks. After I posted my solution it dawned on me that I was trying to use "AND" and not "&" so when I tried it with "&" it worked fine with all the code on one line. I was going back to correct my Post when I saw your solution to the problem was what I had tried. You are correct about getting multiple records but I may be able to use that to my advantage in the future. For now, as long as I get the most recent record, I'm happy.
OK, now how do I rewrite this code to put info back into a table?? It sure would be nice to dump it back into the RecordSource somehow.
Thanks again for your help,
Don

 

[pbaldy]

Not sure what you mean. Your original code was making it the record source.

 

[Knildon]

Sorry for the confusing wording. I'm new to Access and this type of coding so I don't always say what I mean.
The original question was to help me pull data from a table using 2 parameters so I could display the results. That now works fine.
I also have an unbound form that is filled in by the user and partially populated from records in tables based on the user's input. I then save this combined info to a record in another table. The method I dreamed up to save this info works but I don't feel comfortable with it because I saw it skip a record once or twice. Maybe something I did or maybe a glitch. It also causes the screen to flicker because one of the forms opens and closes during the save. My comment in the last post was somewhat of a dig at how easy it is to obtain info from a table through the record source and how hard it is to save info back to a table. Sorry, I didn't mean to cloud the original issue with that comment. I'm happy that part is working great.
If I lose any more hair over this other issue, I guess I'll have to post it for help.
Thanks again,
Don