Access Query to SQL statement

Access Query to SQL statement

Post by Scott » Thu, 11 Apr 2002 23:19:04



I have an Access database program that I am currently
rewriting in VB and SQL Server. I'm trying to take the
Access query information and build code in VB. I'm new to
VB and SQL. I viewed the Access Query in SQL view and I
got the following:

SELECT PLATS.*, PLATS.[LIBER/PAGE]
FROM PLATS
WHERE (((PLATS.[LIBER/PAGE])=[ENTER LIBER/PAGE L44/100-
CONDO NO L420]));

I want the user to be able to type a number in an input
box. The following is the code I wrote in VB:

Private Sub cmdSrchNo_Click()
   Dim cnnConnection As ADODB.Connection
   Dim rstPlatLiber As ADODB.Recordset
   Dim strQuery As String
   Dim lngRecCount As Long
   Dim prompt$
   Dim SearchStr$

   prompt$ = "Enter the Liber/Page (L1/1) or Condo No.
(L100)."
   SearchStr$ = InputBox(prompt$, "Liber/Page or Condo No.
Search")

   strQuery = "SELECT PLATS.*, PLATS.[LIBER/PAGE]" & _
              "From PLATS" & _
              "WHERE (((PLATS.[LIBER/PAGE])=SearchStr$))"

I didn't include the rest of the code for space. I get an
error whenever I try to execute this. Is my SQL SELECT
statement correct? I would appreciate any help.

Scott

 
 
 

Access Query to SQL statement

Post by Michae » Thu, 11 Apr 2002 23:55:35


you're missing spaces in the query, remember the string you create will be
exactly how the database engine will interpret it.

   strQuery = "SELECT PLATS.*, PLATS.[LIBER/PAGE]" & _
              "From PLATS" & _
              "WHERE (((PLATS.[LIBER/PAGE])=SearchStr$))"

will look like this in a string

SELECT PLATS.*, PLATS.[LIBER/PAGE]From PLATSWHERE (((PLATS.[LIBER/PAGE])...

Another thing you have to do is take SearchStr$ out of the quotes, or else
the database will try to match the literal string "SearchStr$" against your
column.

"WHERE (((PLATS.[LIBER/PAGE])='" & SearchStr$ & "'))"

Michael


Quote:> I have an Access database program that I am currently
> rewriting in VB and SQL Server. I'm trying to take the
> Access query information and build code in VB. I'm new to
> VB and SQL. I viewed the Access Query in SQL view and I
> got the following:

> SELECT PLATS.*, PLATS.[LIBER/PAGE]
> FROM PLATS
> WHERE (((PLATS.[LIBER/PAGE])=[ENTER LIBER/PAGE L44/100-
> CONDO NO L420]));

> I want the user to be able to type a number in an input
> box. The following is the code I wrote in VB:

> Private Sub cmdSrchNo_Click()
>    Dim cnnConnection As ADODB.Connection
>    Dim rstPlatLiber As ADODB.Recordset
>    Dim strQuery As String
>    Dim lngRecCount As Long
>    Dim prompt$
>    Dim SearchStr$

>    prompt$ = "Enter the Liber/Page (L1/1) or Condo No.
> (L100)."
>    SearchStr$ = InputBox(prompt$, "Liber/Page or Condo No.
> Search")

>    strQuery = "SELECT PLATS.*, PLATS.[LIBER/PAGE]" & _
>               "From PLATS" & _
>               "WHERE (((PLATS.[LIBER/PAGE])=SearchStr$))"

> I didn't include the rest of the code for space. I get an
> error whenever I try to execute this. Is my SQL SELECT
> statement correct? I would appreciate any help.

> Scott


 
 
 

Access Query to SQL statement

Post by Gerard Lerma » Fri, 12 Apr 2002 00:27:43


strQuery = "SELECT PLATS.*, PLATS.[LIBER/PAGE]" & _

Quote:>               "From PLATS" & _
>               "WHERE (((PLATS.[LIBER/PAGE])=SearchStr$))"

if  SearchStr$ is  variable, you must concatinate them as follows and if
SearchStr$ is a string, u must put it in single quotes:

strQuery = "Select Plats.*,PLATS.[LIBER/PAGE] " & _
                 "FROM PLATS " & _
                 "WHERE PLATS.[LIBER/PAGE] = " & "'" & SearchStr$ & "'"
so this should work!  also after each vb line after & you dont have spaces
like if you have this:
   "HELLO" & _
  "WORLD"   you will get HELLOWORLD not HELLO WORLD. so do this
 "HELLO " & _
"WORLD" and you will get HELLO WORLD

Get it!
-gerard


Quote:> I have an Access database program that I am currently
> rewriting in VB and SQL Server. I'm trying to take the
> Access query information and build code in VB. I'm new to
> VB and SQL. I viewed the Access Query in SQL view and I
> got the following:

> SELECT PLATS.*, PLATS.[LIBER/PAGE]
> FROM PLATS
> WHERE (((PLATS.[LIBER/PAGE])=[ENTER LIBER/PAGE L44/100-
> CONDO NO L420]));

> I want the user to be able to type a number in an input
> box. The following is the code I wrote in VB:

> Private Sub cmdSrchNo_Click()
>    Dim cnnConnection As ADODB.Connection
>    Dim rstPlatLiber As ADODB.Recordset
>    Dim strQuery As String
>    Dim lngRecCount As Long
>    Dim prompt$
>    Dim SearchStr$

>    prompt$ = "Enter the Liber/Page (L1/1) or Condo No.
> (L100)."
>    SearchStr$ = InputBox(prompt$, "Liber/Page or Condo No.
> Search")

>    strQuery = "SELECT PLATS.*, PLATS.[LIBER/PAGE]" & _
>               "From PLATS" & _
>               "WHERE (((PLATS.[LIBER/PAGE])=SearchStr$))"

> I didn't include the rest of the code for space. I get an
> error whenever I try to execute this. Is my SQL SELECT
> statement correct? I would appreciate any help.

> Scott

 
 
 

Access Query to SQL statement

Post by Allan Sun [M » Sat, 13 Apr 2002 11:03:32


Hello,

Please check the code
From:
====
   strQuery = "SELECT PLATS.*, PLATS.[LIBER/PAGE]" & _
              "From PLATS" & _
              "WHERE (((PLATS.[LIBER/PAGE])=SearchStr$))"
To:
====
   strQuery = "SELECT PLATS.*, PLATS.[LIBER/PAGE]" & _
              "From PLATS" & _
              "WHERE (((PLATS.[LIBER/PAGE])='" & SearchStr$ & "'))"

Hope it helps!

Best regards,
Allan Sun
This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

1. SQL Trace to get SQL statement of Access query

I want to use SQL trace to get the SQL statement of an Access query, which
has two subqueries, in order to create a strored procedure with this SQL
statement.

It doesn't however give the "group by" statements or the "SUMs" and various
others.  For another very similar query, SQL trace (or SQL Server Profiler
for version 7) does give the full sql statement (with "group by"s and "SUM"
and others).  I am assuming that the group bys and SUMs are carried out by
Access and not SQL Server.  How can I get the SQL statement?

2. How to know if table exists?

3. VB SQL Statement vs Access Query

4. ASP with Oracle 8 vs. Oracle 7?

5. IIF function in sql statement equal to what is used in Access-Query

6. help newbie with email from d3?

7. IF Then statement in an ACCESS SQL query

8. ODBC Query Timeout Expired problem

9. SQL7 bug-2nd query in a T-SQL statement with a compute statement loses headers

10. if statement and SQL statement in Access

11. Is it possible to use MDX Statements/Queries in QUERY Analyzer(SQL Server)

12. sql statement: ASP v. SQL QUERY Analyzer

13. SQL equivalant for an Access TRansform Sql statement ???