Concatenation and variables...

[Nightowl4933]

Hello. Me again

I have a form which is used as a 'launchpad' for users to search for records meeting specific criteria and it's working - with one exception!

I would like to use a variable in the search string so that, for example, a Post Code search can be carried out on parts of it, rather than the whole lot.

To put together the string, I'm using the following...

strReference = "Application Reference Like '*" & strReference & "*'"

...and another bit like this...

strPostCode = "Post Code = '*" & strPostCode & "*'"

...but I get a VBA error for the top one:
Run-time error '3075':
Syntax error (missing operator) in query expression 'Applicatio Reference Like '*15/*'"
...and a similar error for the other one.

What I'm trying to do is pass these variables from one form to limit the number of records on the next.

Also, is it possible to use multiple variables to do this?

Could someone point me in the right direction, please?

Thanks

 

[The_Doc_Man]

Are you passing field names with spaces in them?

strPostCode = "Post Code = '*" & strPostCode & "*'"

Perhaps you meant to pass this?

strPostCode = "[Post Code] = '*" & strPostCode & "*'"

If the control name or field name is [Post Code], then you can NEVER reference it without the brackets. (Which is why we NEVER suggest inclusion of spaces in anything other than static labels.)

 

[Nightowl4933]

I'll try the brackets, thanks...

 

[Nightowl4933]

Bingo, that did it. Thanks The_Doc_Man

Did you see the other bit about opening the form where two or more criteria can be used?

Should I use And?

Pete

 

[Nightowl4933]

I'm trying to extend my knowledge with this in using several variable to open the new form.

Thanks to The_Doc_Man for answering my query above, but I'm now trying to open the same form based on the value of two variables (and will extend this to three or four!).

So, I have two String variables which I assume I'll have to concatenate in to a single string for the DoCmd instruction, so this is what I did:

strAuthority = "[Authority] = '" & strAuthority & "'"
strLocation = "[Town] = '" & strLocation & "'"

...and these work fine individually, so I concatenated them like this:

strSearchString = strAuthority & " And " & strLocation

...then followed that with this:

DoCmd.OpenForm "frmSearchResult", , , strSearchString

...but I get a "Run-time error '2501' The openForm action was canceled."

The strSearchString variable looks OK, so I'm not sure why it won't work.

A record does exist with both criteria.

Thanks.

 

[Cronk]

When you write
"strSearchString variable looks OK"
does that mean you have examined the value.

Put before the openform line
Debug.print strSearchString
and look at the value in the immediate window

Also, are both table fields included in the form's recordsource?

 

[Nightowl4933]

Woo hoo! Sorted

I simplified it with the following:

strSearchString = "[Authority] = '" & strAuthority & "' AND [Town] = '" & strLocation & "'"

How simple was that?

 

[Nightowl4933]

Well, I thought I was getting somewhere, but now I have a problem using two date fields to limit the records shown in the form :-(

Using this string:

strSearchString = "[Date of Application] Between '#" & Format(dStart, "mm/dd/yyyy") & "# And #" & Format(dEnd, "mm/dd/yyyy") & "'#"

...or this one...

strSearchString = "[Date of Application] Between '#" & dStart & "# And #" & dEnd & "#'"

I get the I get "Run-time error '2501' The openForm action was canceled." again.

The Immediate shows the respective resulting strings as:

[Date of Application] Between '#01/01/2015# And #31/03/2016#'
[Date of Application] Between '#01/01/2015# And #03/31/2016#'

...but I can't get past the Run-time error.

Any ideas, please?

 

[stopher]

Get rid of the single quotes from your expression. Dates don't require quotes, only hashes (#)

 

[Nightowl4933]

Yep, I did that too, but I get the same error.

In that instance the string is:

strSearchString = "[Date of Application] Between #" & Format(dStart, "mm/dd/yyyy") & "# And #" & Format(dEnd, "mm/dd/yyyy") & "#"

...and the Immediate window shows this...

[Date of Application] Between #01/01/2015# And #03/31/2016#

...but no joy :-(

Pete

 

[Cronk]

My post #6
"Also, are both table fields included in the form's recordsource?"

What is the sql of the form's recordsource and what is the command you are using to open the form?

Do you have any code in the Form_open event?

 

[Nightowl4933]

Erm, a little bit embarrassing this.

I was speed-reading and missed some advice.

Having used that advice, all the search criteria options work - all I have to do now is sort out a pesky combo box question, which will follow shortly...

Thank you

 

[Nightowl4933]

All sorted. Thanks everyone