where is the syntax error?

Could anyone tell me what is wrong with my query syntax?

code:

strConditionDates = "(((Mission_Date >= #" & txtDate1 & "#) AND (Mission_Date <= #" & txtDate2 & "#)) AND (Mission_Code = " & Me.ListBoxMission_Code & "))"

Set rstMissions = CurrentDb.OpenRecordset("select distinct FK_Mission_PK from [sql_équipage des missions] where ((FK_MISSION_PK is not null) AND " & strConditionDates)

with the debugger strConditionDates takes the following value:

"(((Mission_Date >= #2005-01-01#) AND (Mission_Date <= #2005-10-05#)) AND (Mission_Code = AI 165 C))"

The value for Mission_Code needs to be wrapped with single quotes.

VB Code:

strConditionDates = "(((Mission_Date >= #" & txtDate1 & "#) AND (Mission_Date <= #" & txtDate2 & "#)) AND (Mission_Code = '" & Me.ListBoxMission_Code & "'))"

I changed it but I now get this message

On the first Mission_Date check (right after the first AND) you have three opening (.

So, either one of them needs to be removed, or an additional ) needs to be added to the end.

-tg

this is driving me nuts :afrog:

ok...here`s what I came up with and still it is not functionning :mad:

this is what I see in the The watch when I run the debugger:

"((Mission_Date >= #2005-01-01#) AND (Mission_Date <= #2005-10-05#) AND (Mission_Code = 'AI 165 C'))"

It looks like you still have mismatched brackets. It is not necessary to bracket each field just the expressions you want to evaluate together.

Assuming I interpreted your intentions correctly, try

VB Code:

strconditiondates = "( (Mission_Date >= #2005-01-01# AND Mission_Date <= #2005-10-05#) 
AND Mission_Code = 'AI 165 C' )"
 
strsql = "select distinct FK_Mission_PK from [sql_équipage des missions] 
where FK_MISSION_PK is not null AND " & strconditiondates

The outcome should be

VB Code:

select distinct FK_Mission_PK from [sql_équipage des missions] 
where FK_MISSION_PK is not null AND ( (Mission_Date >= #2005-01-01# AND Mission_Date <= #2005-10-05#) AND Mission_Code = 'AI 165 C' )

Is this valid syntax? FK_MISSION_PK is not null
Shouldn't it read: Not FK_MISSION Is Null ?

Is Not Null is valid syntax.

My query was functionning until I added strConditionDates. Therefore I assume that "is not full" works.....

I have tried all suggestions listed above but no succces.

Any other suggestions?

thanks

VB Code:

strConditionDates = "[Mission_Date] >= #" & txtDate1 & "# AND [Mission_Date] <= #" & txtDate2 & "# AND [Mission_Code] =‘" & Me.ListBoxMission_Code & "’""
 
Set rstMissions = CurrentDb.OpenRecordset("select distinct FK_Mission_PK from [sql_équipage des missions] where [FK_MISSION_PK] is not null AND " & strConditionDates)

just a try