Syntax of a SQL statement containing a buil-in function

  • Thread starter Thread starter Mota
  • Start date Start date
M

Mota

Hello;
I have a table named PrescriptionsTBL ,having a TEXT field named [PatName].I
need a SQL statement that select all fields of this table where the first 3
letters of the field PatName are 'Pat'.So the Sql WITHOUT its quotations
will be something like that:
StrSql="Select * From PrescriptionsTBL Where left([PatName],3)=Pat"
Can anyone please help me to write this statement?I will be so grateful to
you.
Thank you.
 
The problem is to use quotations for a Built-in function in the Sql
Statement,not the phrase pat.the pat can be easily ='Pat' in the string,and
no need to use an additional function to add this quotes.But i dont know how
to use quotations for the word Left and its parantheses.With the syntax u
wrote,Left wd be evaluated as a field name or something relating to the
table,not as the Function "Left",So the StrSql is wrong and doesnt work.Can
u plz gelp me?
Thank you.

Matt Weyland said:
One easy way is to create a function to add quotes around
the string that you pass to it.

Public Function AddQuotes(str as string) as string
AddQuotes = vbchr(34) & str & vbchr(34)
end Function

Then re-write your SQL statement
strSQL = "Select * From PrescriptionsTBL Where left
([PatName],3)= " & AddQuotes("pat")

HTH

MW
-----Original Message-----
Hello;
I have a table named PrescriptionsTBL ,having a TEXT field named [PatName].I
need a SQL statement that select all fields of this table where the first 3
letters of the field PatName are 'Pat'.So the Sql WITHOUT its quotations
will be something like that:
StrSql="Select * From PrescriptionsTBL Where left ([PatName],3)=Pat"
Can anyone please help me to write this statement?I will be so grateful to
you.
Thank you.


.
Click to expand...
Click to expand...
 
You can convert store the value as a string then play
with it.

dim strPatch as string
srtpatch = left(......)

then pass in strPatch into the DLookup Function.

If all else fails you can simply create a recordset, get
the value you want, then pass it into the insert
statement.

If you have any other questions just let me know.
-----Original Message-----
The problem is to use quotations for a Built-in function in the Sql
Statement,not the phrase pat.the pat can be easily ='Pat' in the string,and
no need to use an additional function to add this quotes.But i dont know how
to use quotations for the word Left and its parantheses.With the syntax u
wrote,Left wd be evaluated as a field name or something relating to the
table,not as the Function "Left",So the StrSql is wrong and doesnt work.Can
u plz gelp me?
Thank you.

Matt Weyland said:
One easy way is to create a function to add quotes around
the string that you pass to it.

Public Function AddQuotes(str as string) as string
AddQuotes = vbchr(34) & str & vbchr(34)
end Function

Then re-write your SQL statement
strSQL = "Select * From PrescriptionsTBL Where left
([PatName],3)= " & AddQuotes("pat")

HTH

MW
-----Original Message-----
Hello;
I have a table named PrescriptionsTBL ,having a TEXT field named [PatName].I
need a SQL statement that select all fields of this
Click to expand...
table
where the first 3
letters of the field PatName are 'Pat'.So the Sql
Click to expand...
WITHOUT
its quotations
will be something like that:
StrSql="Select * From PrescriptionsTBL Where left ([PatName],3)=Pat"
Can anyone please help me to write this statement?I
Click to expand...
will
be so grateful to
you.
Thank you.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Mota said:
The problem is to use quotations for a Built-in function in the Sql
Statement,not the phrase pat.the pat can be easily ='Pat' in the
string,and no need to use an additional function to add this
quotes.But i dont know how to use quotations for the word Left and
its parantheses.With the syntax u wrote,Left wd be evaluated as a
field name or something relating to the table,not as the Function
"Left",So the StrSql is wrong and doesnt work.Can u plz gelp me?
Thank you.
Click to expand...

I don't know why you say this, Mota. If you execute the statement from
Access, and not as a pass-through query, the Left() VBA function will
certainly be recognized (unless maybe you also have a field or table
named Left participating in the query -- I'm not sure about that). If
you run the query from some other application, such as VB -- *not*
Access VBA -- then the Access expression service that evaluates the VBA
functions for queries won't be available, but you haven't said you're
doing that.

If your statement doesn't work, please post the actual statement, the
code that runs it, and the error message you get.
 
Back
Top