Access Select Query not working with a form field selection.

[Snoww]

G]C:\Users\ShelH\Desktop\Access1[/IMG]
here is the query code

here is the whole SQL query coding
SELECT [Trans Rvsd6292017].emplid, [Trans Rvsd6292017].gender, [Trans Rvsd6292017].dob, [Trans Rvsd6292017].name, [Trans Rvsd6292017].stint, [Trans Rvsd6292017].effdt, [Trans Rvsd6292017].record_type, [Trans Rvsd6292017].hire, [Trans Rvsd6292017].term, [Trans Rvsd6292017].jobchange, [Trans Rvsd6292017].prom, [Trans Rvsd6292017].promtype, [Trans Rvsd6292017].storechange, [Trans Rvsd6292017].ratechange, [Trans Rvsd6292017].demotion, [Trans Rvsd6292017].statuschange, [Trans Rvsd6292017].jobcd, [Trans Rvsd6292017].hrlyrt, [Trans Rvsd6292017].logo, [Trans Rvsd6292017].city, [Trans Rvsd6292017].state, [Trans Rvsd6292017].dm, [Trans Rvsd6292017].vpro, [Trans Rvsd6292017].vp, [Trans Rvsd6292017].class, [Trans Rvsd6292017].code, [Trans Rvsd6292017].reason, [Trans Rvsd6292017].store, [Trans Rvsd6292017].emptype
FROM [Trans Rvsd6292017]
WHERE ((([Trans Rvsd6292017].gender) Like "*" & [Forms]![Open Job History Search Form]![gender] & "*") AND (([Trans Rvsd6292017].effdt) Between [Forms]![Open Job History Search Form]![txtstartdate] And [Forms]![Open Job History Search Form]![txtenddate]) AND (([Trans Rvsd6292017].record_type) Like "*" & [Forms]![Open Job History Search Form]![Record_Type] & "*") AND (([Trans Rvsd6292017].promtype) Like "*" & [Forms]![Open Job History Search Form]![promtype] & "*") AND (([Trans Rvsd6292017].jobcd) Like "*" & [Forms]![Open Job History Search Form]![jobcd] & "*") AND (([Trans Rvsd6292017].logo) Like "*" & [Forms]![Open Job History Search Form]![logo] & "*") AND (([Trans Rvsd6292017].city) Like "*" & [Forms]![Open Job History Search Form]![city] & "*") AND (([Trans Rvsd6292017].state) Like "*" & [Forms]![Open Job History Search Form]![state] & "*") AND (([Trans Rvsd6292017].dm) Like "*" & [Forms]![Open Job History Search Form]![dm] & "*") AND (([Trans Rvsd6292017].vpro) Like "*" & [Forms]![Open Job History Search Form]![vpro] & "*") AND (([Forms]![Open Job History Search Form]![store]) Is Null)) OR ((([Trans Rvsd6292017].store)=[Forms]![Open Job History Search Form]![store]));

Need it to pull all results based on that query SQL but it is stopping with only two parameters leaving all the others not pulling.

 

[stumac]

Possibly to do with the OR statement at the bottom and the positioning of your brackets...
Also using access query builder is fine until you try to troubleshoot - then you get brackets added in all over the place which make it confusing to read. I have aliased your table below to make it easier to read and taken out/moved some of the bracketing to see if that helps.

I would also advise against so many wildcards as they can throw up starnge results (if your gender is in a table as Male and Female and a user enters M in the form this will show both Males and Females), I would recommend in situations like this you limit the selections with a combo box and in the query test for null as you have done with the 'store' field.

SELECT TR.emplid, TR.gender, TR.dob, TR.name, TR.stint, TR.effdt, TR.record_type, TR.hire, TR.term, TR.jobchange, TR.prom, TR.promtype, TR.storechange, TR.ratechange, TR.demotion, TR.statuschange, TR.jobcd, TR.hrlyrt, TR.logo, TR.city, TR.state, TR.dm, TR.vpro, TR.vp, TR.class, TR.code, TR.reason, TR.store, TR.emptype

FROM [Trans Rvsd6292017] AS TR

WHERE TR.gender Like "*" & [Forms]![Open Job History Search Form]![gender] & "*" AND 
TR.effdt Between ([Forms]![Open Job History Search Form]![txtstartdate] And [Forms]![Open Job History Search Form]![txtenddate]) AND 
TR.record_type Like "*" & [Forms]![Open Job History Search Form]![Record_Type] & "*" AND 
TR.promtype Like "*" & [Forms]![Open Job History Search Form]![promtype] & "*" AND 
TR.jobcd Like "*" & [Forms]![Open Job History Search Form]![jobcd] & "*" AND 
TR.logo Like "*" & [Forms]![Open Job History Search Form]![logo] & "*" AND 
TR.city Like "*" & [Forms]![Open Job History Search Form]![city] & "*" AND 
TR.state Like "*" & [Forms]![Open Job History Search Form]![state] & "*" AND 
TR.dm Like "*" & [Forms]![Open Job History Search Form]![dm] & "*" AND 
TR.vpro Like "*" & [Forms]![Open Job History Search Form]![vpro] & "*" AND 
([Forms]![Open Job History Search Form]![store] Is Null OR TR.store=[Forms]![Open Job History Search Form]![store]);

 

[stumac]

Forgot to mention, if you are pulling your data from one source (one table or one query) then you do not need to include the table/query name before the field name. In the above every instance of "TR." can be removed. again just makes it slightly easier to read. If you are going to join another table at some point you are as well keeping it in.

 

[Snoww]

Forgot to mention, if you are pulling your data from one source (one table or one query) then you do not need to include the table/query name before the field name. In the above every instance of "TR." can be removed. again just makes it slightly easier to read. If you are going to join another table at some point you are as well keeping it in.

OK The customer wants all the records and then to allow the person to enter into fields certain data....to pair down the results.

also the above code is saying too complex for access to do.....is there another work around that I can do?
yes it is all from the same table and no we are not going to add additional tables in the future all static data.

 

[stumac]

when I require a complex search like this I always build the query definition on the fly, that way if the choices need to change in the future it is fairly easy to follow. It is a bit more complex than what you are currently doing... however, in my opinion it is a better way. First thing to do is create a blank query (in my case this is called MyQuery). Then add the following code to the on click event of the button you are using to open the query on your form:

Dim QryText As String
Dim Criteria As String
Dim qd As QueryDef
If Nz(Me.Gender) <> "" Then Criteria = "Gender = '" & Me.Gender & "' AND "
If Nz(Me.txtstartdate) <> "" Then Criteria = "effdt >= #" & Format(Me.txtstartdate, "MM/DD/YYYY") & "# AND "
If Nz(Me.txtenddate) <> "" Then Criteria = Criteria & "effdt <= #" & Format(Me.txtenddate, "MM/DD/YYYY") & "# AND "
If Nz(Me.Record_Type) <> "" Then Criteria = Criteria & "Record_Type = '" & Me.Record_Type & "' AND "
If Nz(Me.Promtype) <> "" Then Criteria = Criteria & "Promtype = '" & Me.Promtype & "' AND "
'Continue to build your criteria string here

If Criteria <> "" Then
    Criteria = "WHERE " & Left(Criteria, Len(Criteria) - 4) ' remove the final "AND "
End If

QryTxt = "SELECT emplid, gender, dob, [COLOR=#FF0000]name[/COLOR], stint, effdt, record_type, hire, term," _
& "jobchange, prom, promtype, storechange, ratechange, demotion, statuschange, jobcd," _
& "hrlyrt, logo, city, state, dm, vpro, vp, class, code, reason, store, emptype" _
& " FROM [Trans Rvsd6292017] AS TR " & Criteria
Set qd = CurrentDb.QueryDefs("myquery")
qd.SQL = QryTxt
DoCmd.OpenQuery "MyQuery"
Set qd = nothing

You will see that I haven't put all your criteria in, you can continue to build your string. Also I have assumed your fields are text if they are numbers then you should omit the single quotes.

I always build a little, test, build a bit more, test some more...

also I note that you have a field name called 'Name' this is a reserved name in Access - you can't usually call a field this.