Wildcard in table? (1 Viewer)

[coyne]

Hello
how can I make a query to search a table with wildcards in the table.

example

table:

jobA*
jobB*

searches:

jobA11 would return jobA*
jobA22 would return jobA*
jobB11 would return jobB*
.
.

Thanks Im kinda new to this so any help is appreciated.

 

[Fizzio]

Normally you would do the opposite ie store the specifics ie JobA11 etc and search for a wildcard ie JobA*. Is there a reason why you are doing it the other way?

 

[coyne]

I would like to use this data base to pull up a job on my system and find the programer name and failure actions. there is a ton of jobs and many of them have the same failure action and programer name. The jobs naming conventions are in a way that being able to use wild cards in the table would save space and data entry time. I may be going about this the wrong way. Any ideas?

thanks

 

[coyne]

I was thinking somthing like this but I dont know how to code the query in .

joblen = Len(jobin)

While joblen <> 0
jobalt = Left(jobin, joblen) & "*"
joblen = joblen - 1

????

Wend

 

[coyne]

This is what I ended up doing. It seems to work ok. This is my first vba script so if I'm missing anything or theirs a more efficient way, let me know. also how do I make the input case sensitive.



Dim joblen
Dim jobalt
Dim Sql_Str As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

jobalt = jobin
joblen = Len(jobin)

Do While joblen

Sql_Str = "Select [Job Name] from Ascall_jobs where [job Name] ='" & jobalt & "'"
Set rs = db.openrecordset(Sql_Str)

If rs.RecordCount = 1 Then
DoCmd.OpenForm "CALL_INFO", acNormal, , "[job Name] ='" & jobalt & "'"
Exit Do
End If

jobalt = Left(jobin, joblen) & "*"
joblen = joblen - 1

Loop