Using field data to generate a query

[lopster10]

Hi guys,

I have a combo box which is populated when an Access form is loaded. This selects all users from a particular location, and then displays the data within the combo box as Surname, FirstName. The user can then select the necessary user they are after. (So the 2 values are put together to form 1 string)

Based on what the user selects I then need to run another query using the Firstname and surname as seperate parameters. As in select ID from a table where FieldName = Name and FieldSurname = surname.

How can I seperate the name and surname to use in my next query?

Any help appreciated

 

[PHV]

Have a look at the Split function.
You may also consider the InStr and Mid functions.

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

 

[lopster10]

Ok, thanks for that, just 1 more question..

Is it possible to assign the return values to variables within the query statement

So if for example I had the following:

"SELECT tblPersons.[Start Date], tblPersons.[Expiry Date]
FROM tblPersons
WHERE tblPersons.[Name] = '" & ValueName & "';"


Could I assign the [Start Date] return value and [expiry Date] return value to different variables within the above statement?

Regards.

 

[PHV]

If you don't know how to play with a Recordset object you may consider the DLookUp function.

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

 

[lopster10]

Sorry! Im pretty new to VBA and I have just realised how to do it after seeing the word Recordset!

Thanks