Create a text variable with " in it

D

Djblois

Registered User.
Local time
Today, 06:03
Joined
Jan 26, 2009
Messages
598
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?
 
how about

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

you might need Chr(34) (i think 34 is right)
 
I tried that way also. How would I use chr(34)? how would that work?
 
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)).
 
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?
 
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.
 
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;
 
think we need to see the function. does the function work by itself?
 
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.
 
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"))...
 
ok that sounds like it might work but how do I do that?
 
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.
 
...AND ((Scheduled_Appts.WhseID)="1 or 2"))...
Click to expand...

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!
 
gemma-the-husky said:
I think you could also use

if fieldname in ("1","2")
Click to expand...
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)
 
You must log in or register to reply here.

Similar threads

I
Create and training record database
Replies
8
Views
1,098
Pat Hartman
P
J
Solved Caculate the business day based on date time field
Replies
19
Views
1,329
Gasman
Gasman
J
Field to enter ONLY time.
2
Replies
29
Views
1,622
Mark_
M
J
Solved Column in Text Box
Replies
10
Views
793
JoséBob
J
asteropi
Solved How to format ascending numbers for different inputs?
2
Replies
39
Views
2,429
asteropi
asteropi

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom