String manipulation

  • Thread starter Thread starter Contro
  • Start date Start date
C

Contro

Hi Guys,

I'm stuck on trying to find a row of data which has a field containing
a year specified by the user.

My code is:

SELECT Applications.[Surname at exam], Applications.[Surname now],
Applications.[First Name(s)], Applications.[Date of Birth],
Applications.[Date Application Received], Applications.[Qualification
Type(s)], Applications.[Year(s) of Qualification],
Applications.[Statement/Cert Letter/], Applications.[Amount Received],
Applications.[Method of Payment], Applications.ID,
Applications.[Outline Sent], Applications.[U Letter Sent],
Applications.[Despatch Date], Applications.[Other Information]
FROM Applications
WHERE Applications.[Year(s) of Qualification] LIKE "*7#*";

The bottom line is the main part. Basically, this will find any year
that is in the 70's (in formats "1975", "75", "65,86,72"). But I want
the user to be able to specify the decade...but when I change the last
line to

WHERE Applications.[Year(s) of Qualification] LIKE "*[choice]#*";

it obviously doesn't recognise that it's a variable, but thinks the
[choice] is part of the string...I've tried using brackets in various
places and things like that, but nothing works! Please help!

Thank you very much in advance. Your help will be hugely appreciated!
:-)))))

Contro.
 
Hi Contro,

In order to query for a date in Access you need to encapsulate it in
hashmarks - ie, the date 1/1/2006 would need to be stated as #1/1/2006#
 
That's brilliant! Thanks, guys! Sorry it took me so long to reply -
I've been a bit busy recently, and only just got round to looking at
your replies. Thank you so much again!

Are there any good reference guides on the internet for access and sql?
I tried searching on the interweb, but all I get are lots of sites
which mainly talk about vb code and access...I just want the "normal"
sql stuff.

Don't worry if you can't though, as you've already been a great help!
Just thought it would be handy, as I wouldn't have to bother you guys
all the time if I get any more problems :-)

Hope you are all having a good weekend!

Contro.


Lynn said:
Your query sees '[choice]' as a part of a string. You need to pull it out so
that it will recognize it as a parameter and then concatenate it with the
rest of your Like clause

LIKE "*" & [choice] & "#*"

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Contro said:
Hi Guys,

I'm stuck on trying to find a row of data which has a field containing
a year specified by the user.

My code is:

SELECT Applications.[Surname at exam], Applications.[Surname now],
Applications.[First Name(s)], Applications.[Date of Birth],
Applications.[Date Application Received], Applications.[Qualification
Type(s)], Applications.[Year(s) of Qualification],
Applications.[Statement/Cert Letter/], Applications.[Amount Received],
Applications.[Method of Payment], Applications.ID,
Applications.[Outline Sent], Applications.[U Letter Sent],
Applications.[Despatch Date], Applications.[Other Information]
FROM Applications
WHERE Applications.[Year(s) of Qualification] LIKE "*7#*";

The bottom line is the main part. Basically, this will find any year
that is in the 70's (in formats "1975", "75", "65,86,72"). But I want
the user to be able to specify the decade...but when I change the last
line to

WHERE Applications.[Year(s) of Qualification] LIKE "*[choice]#*";

it obviously doesn't recognise that it's a variable, but thinks the
[choice] is part of the string...I've tried using brackets in various
places and things like that, but nothing works! Please help!

Thank you very much in advance. Your help will be hugely appreciated!
:-)))))

Contro.
Click to expand...
Click to expand...
 
Back
Top