Query from form with the like statement

[Petruche]

Hello,

I'm building a query based on user input from a form, the problem is that the user has to input the exact query term to retrieve information.

It looks like this in the criteria section of my query: [Forms]![drawing20041209]![Drawing Number]

Is there a way to use the 'like' statement in the criteria section so a user can input just part of the query term ?

Thanks

 

[lespaul]

Like [Forms]![drawing20041209]![Drawing Number] & "*"

Leslie

 

[Petruche]

Thanks, I just had to modify it to
Like "*" & [Forms]![drawing20041209]![Drawing Number] & "*

 

[Petruche]

All right,
additionnal question:

There's about 10 fields the user can search on, the problem is that if a field is empty, there are no records returned. How can I have the query return records even if a field is blank ?

Thanks

Pierre

 

[dhookom]

Petruche,
Are the results displayed in a report or form (recommended) or are you opening a query in datasheet? There are significant differences in solutions based on your requirements.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Petruche]

Duane,

A little history:

I have a drawings table and I want users to be able to search that table based on one or more criterias. I created a form with all the fields contained in that table and a search button that calls the query. The query has the criteria fields set to get data from the user input from my form like this:

Like "*" & [Forms]![advanced search drawings]![whatever field] & "*".

I need the results to be displayed in a list, I used to have the results displayed one by one with a basic search but users complained that they had to click "next" through the results all the time...

The results are displayed in a datasheet now, which is ok.

Pierre

BTW, how's the grandson ? ;o)

 

[dhookom]

[blue]"I need the results to be displayed in a list"[/blue]
This doesn't answer the one question that I asked. There is a list box in Access but no object named "list". You can display multiple records in a report, form, datasheet view of a form or query, or list box.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Petruche]

"The results are displayed in a datasheet now, which is ok."

In other words, the query returns a datasheet view of the results.

I thought that answered the question. I also wanted to explain what I wanted to achieve, the more details the better.

I'm still learning all this on my own with no programming background so please bare with me.

Thanks

Pierre

 

[dhookom]

Sorry I missed the last statement.
I would create a datasheet view of a form. You can then build a "where" clause for a DoCmd.OpenForm method based on your controls.

Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.txtDrawingNumber) Then
strWhere = strWhere & " AND [DrawingNumber]=""" & _
Me.txtDrawingNumber & """"
End If
If Not IsNull(Me.txtNumericField) Then
strWhere = strWhere & " AND [NumericField]=" & _
Me.txtNumericField
End If
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtStartDate & "#"
End If
DoCmd.OpenForm "frmYourDataSheetForm", acFormDS,, strWhere




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]
[blue]Ask me about my grandson, get a grand answer.[/blue]