'Mid' Questions

[Demona]

Hi Guys,

I am currently using a MID command in a query statement that is built on the fly via code. The question is, is there a better command as this command is difficult to keep formatted. Here is an example of my code

& Mid(SubAreas, 4)

The way this command works is straight forward to me, the problem is that I have multiple check box areas on this form and as long as the user selects from the first area all is well but if a user selects from the second area I get an ( or SubAreas ... ) error. Obviously this is caused by not selecting from the first secton. Now how can this be avoided and is there a better command than MID? My understanding of the MID command is to trunicate certain text from the beginning of the search string depending on the number specified. Keep in mind that I need to be able build a query from the different areas of check boxes with some areas having no selection at all depending on the query required.


Thanks

Love and Kisses

Demona

 

[Fizzio]

Post your full code as you are having problems with null values and the solution will depend on the SQL

 

[Demona]

This is the query that is being built for me by the code

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", "Select * from [Data Output]" & ("where (" & Mid(Shift, 4) & "" & Mid(SubAreas, 4) & "" & Mid(EMO, 4) & ")"))

and the check box codes look like this

If [Oper] = -1 Then
        where = where & " or  [EMO] = " & "'Operational'"
    End If

Thanks

Love and Kisses

Demona

 

[Mile-O]

("where (" & Mid(Shift, 4) & "" & Mid(SubAreas, 4) & "" & Mid(EMO, 4) & ")"))

You are building a field name from four fields and your SQL can't find this field you are defining.

Also, you are building criteria in your SQL but no specifying the criteria i.e WHERE thisfield = what?



Also:

[EMO] = " & "'Operational'"

I think you can change it to this for greater functionality.

[EMO] = ""Operational"""

 

[Demona]

Oops

I guess you need more code to really make an educated guess.

Sorry guys.