Putting 's p a c e s' in the text of a Parameter box

[Carl_R]

How do I put spaces in the text in my Parameter box?

At the moment I have 'spaced' out the words "Enter your Group name" with Underscores. It looks terrible but it works.
If I put hard spaces, I get a syntax error.

Code:
stSQL = stSQL & " WHERE (((Year([ImplementationDate]))= " & Year(txtSelectDate) & ") AND ((Month([ImplementationDate]))= " & Month(txtSelectDate) & ")) And (tblChanges.Group) = " & CStr("Enter_your_group_name") & ""

 

[ColinEssex]

Hi

In the query grid - conditions of the relevant field (presumably groupName) put this-

[Enter your group name]

That should do it

Col

 

[Carl_R]

Thanks Colin but there is no query design grid. The query is generated from the underlying VB/SQL and then deleted - the results are output to a report.

If I use Cstr[bla bla bla] in my VB/SQL, the text is treated as a field (which obviously cannot be found).

I know it's simply syntax but blowed if I know

 

[The_Doc_Man]

I know of no simple format to do this. However, if you don't mind a little VBA code, ....

Public Function DblSpcHorizontal( stIn as string ) as String

Dim loI as Long
Dim stTmp as String
Dim stOut as String

stTmp = Trim$( stIn )
stOut = ""
For loI = 1 to Len( stTmp )
stOut = stOut & Mid$( stTmp, I, 1 ) & " " 'single space betw. quotes
Next loI

InsSpaces = Trim$( stOut )

End Function

OK, put this in any general module. Compile the module. Save it.

Now, whenever you want to use a spaced-out string like that, you could use DblSpcHorizontal("string") in place of "s t r i n g" - and this even works for arbitrary strings.

The underlying trick here is that a public function can be used in any query, form, report, or VBA code. So all you have to do is define this.

 

[Cosmos75]

Did a simple parameter query from a table (tblPerson) with three fields (Person, HireDate and ProbationDate) and added a parameter for HireDate [Date of Hire] so that when you run the query you get an input box to enter a date for the query to filter on HireDate, here what I got

SELECT tblPerson.Person, tblPerson.HireDate, tblPerson.ProbationDate
FROM tblPerson
WHERE (((tblPerson.HireDate)=[Date of Hire]));

so can't Carl_R's code be this

stSQL = stSQL & " WHERE (((Year([ImplementationDate]))= " & Year(txtSelectDate) & ") AND ((Month([ImplementationDate]))= " & Month(txtSelectDate) & ")) AND (((tblChanges.Group) = [Enter your group name]));

Does it make a difference if you use And instead of AND?

 

[Pat Hartman]

Your syntax is incorrect:

stSQL = stSQL & " WHERE (((Year([ImplementationDate]))= " & Year(txtSelectDate) & ") AND ((Month([ImplementationDate]))= " & Month(txtSelectDate) & ")) And (tblChanges.Group) = '" & [Enter your group name] & "'"

You need square brackets around any field that contains embedded spaces or special characters. You also need single (or double) quotes around the paramete since it is a string.

 

[Carl_R]

What's in a ' " ' ?

Sorted! Thanks all for your help