Create a text variable with " in it (1 Viewer)

Djblois · Jul 1, 2009

I am creating a variable and I need to put a couple of quotes in the variable. But whenever I type a quotes it thinks I am closing the quotes. how do I get it to accept the " as is. Here is what I tried:

' "01" or "02" '
"01" or "02"
"01' " ' or ' " ' 02"
"01" & 'or' & "02"

none of them work. How do I do it?

wazz · Jul 2, 2009

how about

" ""01"" or ""02"" "

you might need Chr(34) (i think 34 is right)

Djblois · Jul 2, 2009

I tried that way also. How would I use chr(34)? how would that work?

wazz · Jul 2, 2009

correction:
Chr(34) & "01" & Chr(34) & " or " & Chr(34) & "02" & Chr(34)

both versions work for me (this one and the one that doesn't use Chr(34)).

Djblois · Jul 2, 2009

I am then using that variable in a Query to filter a field. However, it says that the expression is too complex! How do I fix that?

wazz · Jul 2, 2009

need to see everything you've got to determine that.
my guess is that it has to do with the Or part. could also be data types, etc.
again, we need to see more.

Djblois · Jul 2, 2009

Here is the query:

Code:

SELECT Scheduled_Appts.Appt_Date, Scheduled_Appts.Sch_Time, 
Scheduled_Appts.Arr_Time, Scheduled_Appts.Missing_PaperWork, 
Scheduled_Appts.Incoming, Scheduled_Appts.Appt_ID, 
Scheduled_Appts.Outgoing, Scheduled_Appts.Dep_Time, 
Scheduled_Appts.Created_by, Scheduled_Appts.Created_Date, 
Scheduled_Appts.Last_Modified_by, Scheduled_Appts.Last_Modified_Date, 
Scheduled_Appts.Confirmation_Num, Scheduled_Appts.Arr_Date, 
tblWhse.Whse, 
Scheduled_Appts.Car_ID, Scheduled_Appts.WhseID
FROM Scheduled_Appts LEFT JOIN tblWhse ON 
     Scheduled_Appts.WhseID = tblWhse.whseID
WHERE (((Scheduled_Appts.Appt_Date)=[Forms]![frmScheduled_Appts]![cldrApptDates]) 
   AND ((Scheduled_Appts.Incoming)=[Forms]![frmScheduled_Appts]![cbViewRec]) 
   AND ((Scheduled_Appts.WhseID)=getWhse())) 
   OR (((Scheduled_Appts.Appt_Date)=[Forms]![frmScheduled_Appts]![cldrApptDates]) 
   AND ((Scheduled_Appts.Outgoing)=[Forms]![frmScheduled_Appts]![cbViewShip]) 
   AND ((Scheduled_Appts.WhseID)=getWhse()))
ORDER BY Scheduled_Appts.Sch_Time, Scheduled_Appts.Arr_Time;

wazz · Jul 2, 2009

think we need to see the function. does the function work by itself?

Djblois · Jul 2, 2009

Here is what I do before the query:

Code:

If Me.cb01 And Me.cb02 And Me.cbDemed Then
            SaveSetting "WST", "General", "Whse", _
            Chr(34) & "1" & Chr(34) & " or " & Chr(34) & "2" & Chr(34) & " or " & Chr(34) & "3" & Chr(34)
        ElseIf Me.cb01 And Me.cb02 Then
            SaveSetting "WST", "General", "Whse", Chr(34) & "1" & Chr(34) & " or " & Chr(34) & "2" & Chr(34)
        ElseIf Me.cb01 And Me.cbDemed Then
            SaveSetting "WST", "General", "Whse", Chr(34) & "1" & Chr(34) & " or " & Chr(34) & "3" & Chr(34)
        ElseIf Me.cb01 Then
            SaveSetting "WST", "General", "Whse", "1"
        ElseIf Me.cb02 And Me.cbDemed Then
            SaveSetting "WST", "General", "Whse", Chr(34) & "2" & Chr(34) & " or " & Chr(34) & "3" & Chr(34)
            NotAllowEditingAppts
        ElseIf Me.cb02 Then 'Change when 02 opens
            SaveSetting "WST", "General", "Whse", "2"
        ElseIf Me.cbDemed Then
            SaveSetting "WST", "General", "Whse", "3"
        End If

then the function:

Code:

Function getWhse() As String

    getWhse = GetSetting("WST", "General", "Whse")

End Function

and then I do the Query.

Djblois · Jul 6, 2009

Does anybody have any ideas?

Atomic Shrimp · Jul 7, 2009

If I'm reading all of this correctly, you're trying to execute SQL that ends up with an expression in the WHERE clause that looks (from the point of view of the SQL engine) like this:

Code:

...AND ((Scheduled_Appts.WhseID)="1 or 2"))...

What you need to do, I think, is find a way of passing those two test values separately, so you can build an expression something like:

Code:

...AND ((Scheduled_Appts.WhseID)=getSomeValue()) OR (Scheduled_Appts.WhseID)=getSomeOTHERValue()))...

Which (from the point of view of the SQL engine, when it executes the code) will look like:

Code:

...AND ((Scheduled_Appts.WhseID)="1") OR (Scheduled_Appts.WhseID)="2"))...

Djblois · Jul 7, 2009

ok that sounds like it might work but how do I do that?

Atomic Shrimp · Jul 7, 2009

Not sure - problem is, you have a variable number of parameters and a terribly complex set of rules/evaluations. I think you might be better off building the whole SQL in code, then pushing it into whatever object is appropriate.

gemma-the-husky · Jul 9, 2009

...AND ((Scheduled_Appts.WhseID)="1 or 2"))...

is atomicshrimp right?

if you are ending up with this you will be testing for the string 1 or 2, not for 2 alternative strings.

as he says you need the final string to look like this

if fieldname = "1" or fieldname = "2"

I think you could also use

if fieldname in ("1","2")

-----------
it is always awkward constructing a string wioth embedded " characters. If you use them directly you need to put the character twice to get them twice

so (I think)

strg = "if fieldname = ""01"" "

chr(34) is the ascii char for a ", and it is easier to use

so you get

strg = "if fieldname = " & chr(34) & "01" & chr(34)

you can also assign a variable

so

dim strqut as string
strqut = chr(34)
strg = "if fieldname = " & strqut & "01" & strqut

unfortunately you cant use a const

const strqut = chr(34)
const strqut = "
const strqut = "" or even
const strqut = """"

all won't work!

Atomic Shrimp · Jul 9, 2009

gemma-the-husky said:
I think you could also use

if fieldname in ("1","2")

You know, I think that might be the most expedient solution here (the best one, long term, is to try to simplify that whole decision tree process and criteria set, but using the IN function should work, for now)

Create a text variable with " in it (1 Viewer)

Djblois

Registered User.

wazz

Super Moderator

Djblois

Registered User.

wazz

Super Moderator

Djblois

Registered User.

wazz

Super Moderator

Djblois

Registered User.

wazz

Super Moderator

Djblois

Registered User.

Djblois

Registered User.

Atomic Shrimp

Humanoid lifeform

Djblois

Registered User.

Atomic Shrimp

Humanoid lifeform

gemma-the-husky

Super Moderator

Atomic Shrimp

Humanoid lifeform

Similar threads

Users who are viewing this thread