Brackets in A Query Criteria From a Combo Box/Text

[likeyeah]

I have a form that prints a report using a two combo buttons to select criteria.

One is a week number in the form 1151,1152 etc. and the other will deliver a string dependent on the section.

So for exampe using stlinkcriteria I will filter the report on Week 1152 for WTS(M)

The M represents Mechanical, I use an E for Electrical.

When I pass the parameters using :

stlinkcriteria = "[ForecastWeek]=" & Me!fcweektxt & " AND [SectionFull] = " & Me![plnwksectresptxt]

Which are the two combo box selections fed into text boxes fcweektxt and plnwksectresptxt using the "Msgbox
stlinkcriteria" command within the event code I get:


[ForecastWeek]=1151 AND [SectionFull]=WTS(M)

So it is passing the required parameter to the query. However, when you use anything with brackets in a query, it
sees it as a function, so even in direct mode if you type WTS(M) into the criteria field, Access automatically
makes it WTS("M") so you have to edit it manually to make it

"WTS(M)"

Can anyone point me to the right syntax in the stlinkcriteria statement to send the WTS(M) criteria to the
underlying query of the report in the correct way to get parsed properly. I presume it is around using ' and "
correctly to get the data into the query. Or in fact is this not possible due to the way access queries treat
brackets?

Thanks

J

 

[John W. Vinson]

Can anyone point me to the right syntax in the stlinkcriteria statement to send the WTS(M) criteria to the
underlying query of the report in the correct way to get parsed properly. I presume it is around using ' and "
correctly to get the data into the query. Or in fact is this not possible due to the way access queries treat
brackets?

The problem isn't the blanks, it's the lack of the syntatically required
quotmarks. Different datatypes require different delimiters - Date values
require #, Text values a quotemark (either ' or "), Number values should have
no delimiter at all. Access Queries automatially take care of this with
parameters, but a WhereCondition argument in VBA code does need them. Try

stlinkcriteria = "[ForecastWeek]=" & Me!fcweektxt & " AND [SectionFull] = '" &
Me![plnwksectresptxt] & "'"

This will give you
[ForecastWeek] = 1151 AND [SectionFull]='WTS(M)'

If the text argument might contain an apostrophe (names such as O'Niell for
example) you can use doublequotes; to incorporate a doublequote in a string
delmited by doublequotes double the doublequote (how's THAT for doubletalk!):

stlinkcriteria = "[ForecastWeek]=" & Me!fcweektxt & " AND [SectionFull] = """
& Me![plnwksectresptxt] & """"

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[likeyeah]

John,

Many thanks, solution worked fine. I adapted the event to use a If Then so that if it was a wildcard it only used
the week number, otherwise week number and section.

I shall add this further example of syntax to my expanding "how the hell do I sort this syntax out" list which I
am keeping in Word...!

Thanks again