having trouble writing query

[bw2601]

i would like to write a query that will return a value when a user inputs criteria. for example if a user name is entered i would like it to return an ID #. I am having troubles doing this using the iif function. does anyone have any ideas on how i can get started.

this is what i have tried

iff ((text = [id]) text = [name], (([name]) [ID]), Invalid entry.

thank you for taking the time to help me out.

 

[jbpez]

What is 'text' in your Iff function and is there a difference between [id] and [ID]. Please explain more.

John Borges

 

[bw2601]

no there is not a differnce between id and ID it was a typing error i didn't pick up, and text is the text the user enters.

 

[bw2601]

here let me be a little more spcific.

i would like to be able to enter data into a box such as a user name (text)

then would like to have it return the ID. Now if i enter an ID instead of user name I would like it to return the User name.

the user name and the ID are taken out of the same table lets call it user data.

I hope this helps out

 

[jbpez]

Is the ID a number ? How is the result handled ? Show the result in a separate textbox, label, message pop up ?
Do you know VBA ?

John Borges

 

[bw2601]

The ID is a number, and i would like the result to come in the form of a message pop up with an ok button. I am in the Process of learning VBA. so please be gentile.

 

[mjk9564]

I'm not sure what text is but this should get you started.

IF text = ID THEN
msgbox "Your ID is: " & ID, vbOkOnly, "Your Title"
else
msgbox "Invalid Entry", vbokonly, "Your Title"
end if

Are you trying to query a DB from their input?

Hope this helps.
Matt

 

[jbpez]

Try:

On the AfterUpdate Event of the textbox write this

Private Sub NameOfTextBox_AfterUpdate()

  Dim varInput as variant
  Dim varResult as variant
  
  'user input value
  varInput = Me.NameOfTextBox.Value 

  If IsNumber(varInput)=True Then
    'search user table for user name based on user id
    varResult = DLookup("[UserName]", "UserDataTable", _
                "[UserID] = " & varInput)
  Else
    'search user table for user id based on user name
    varResult = DLookup("[UserID]", "UserDataTable", _
                "[UserName] = '" & varInput & "'")
  End if

  Msgbox varResult
  
End Sub

I hope this helps

John Borges