Quotes messing up my query!

[tbellomo]

OK, here's my dilemma:

I'm dynamically creating criteria depending on what day of the week it is. I use a function that I made to determine the criteria. I want the function to return "In(3,4,5)" to the query's criteria (but without the quotes). I just want In(3,4,5), meaning it will look for either 3's, 4's, or 5's. But it's looking for the string "In(3,4,5)" in a numeric field...and returning no results.

Is there anyway to return the value with out the quotes?

Thanks,
Tim

 

[SkipVought]

Tim,

MyInString = "In(3,4,5) "
MySQL = "Select * From MyTable Where Field1 " & MyInString

"-)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tbellomo]

I'm not writing the sql statement in code, so that won't work, but thanks anyway!


Timo

 

[PHV]

Any chance you could post how you try to dynamically build the sql stuff ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tbellomo]

I have a conventional query, and in the criteria spot on one of the fields, I put fncGetDaysCrit().

fncGetDaysCrit returns the value "In(3,4,5)", so the query looks for that exact string rather than values of 3, 4, or 5.

fncGetDaysCrit is set up to return the value like this:

If such and such
fncGetDaysCrit = "In(3,4,5)"
End If

Thanks,
Timo

 

[PHV]

Is playing with a QueryDef object an option for you ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tbellomo]

If that's the only way to do it, i'll look into... I haven't done it before, but i'm sure I can figure it out. I was just wondering if there was a built in function that would strip the quotes out of the criteria to make In(3,4,5) rather than "In(3,4,5)".

Thanks,
Timo

 

[Golom]

Given that you are calling a function anyway, why not just have the function do the test?
[tt]
Public Function fncGetDaysCrit (TheField As Integer) As Boolean
fncGetDaysCrit = (TheField = 3 OR TheField = 4 OR TheField = 5)
End Function
[/tt]
and
[tt]
Select * From MyTable Where fncGetDaysCrit (myField) = TRUE
[/tt]

 

[DrSimon]

To strip out quotes use Replace as in [green]
Tempstr = Replace(TempStr, """", "") [/green]
Simon Rouse

 

[PHV]

fncGetDaysCrit returns the value "In(3,4,5)"
I don't think so. Your function returns a string, In(3,4,5), that is passed as a literal constant to the SQL engine.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tbellomo]

Thanks to everyone for your outstanding effort!

PHV - I'm not so sure, when I have fncGetDayCrit="In(3,4,5)" the query returns data type mismatch.

Doc Simon - It didn't work for my situation, but thanks for the tip... it's gonna help in the future!

Gollom - You hit the nail on the head... when i first saw your suggestion, I was like, "That won't work!" -- Then I tried it. Sorry for second-guessing your code, and thanks a lot!!!

Thanks a 1,000,000;
Timo