SQL Error

Hi there Ive created some SQL code in the Click event of a submit button. I had it working at the start but when i made a few modifications it keep telling me that and end of statement is required . The strings are retrieved from combo box values and then stored in string variables that are assigned to the SQL string.

Expand|Select|Wrap|Line Numbers

 
Private Sub CmdSubmit_Click()
 
Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim strSQL As String

Dim operatingsystem As String

Dim make As String

Dim computertype As String
 
Set db = CurrentDb

Set qdf = db.QueryDefs("Admin_query")
 
If (Me.CboOS.Value = "All") Then

     operatingsystem = " Like '*' "

Else

 operatingsystem = "='" & Me.CboOS.Value & "' "
 
End If
 
If (Me.CboMake.Value = "All") Then

make = " Like '*' "

Else

make = "='" & Me.CboMake.Value = "='" & "' "
 
 End If
 
'below is only two of the many SQL variations ive tried
 
 '  strSQL = "SELECT laptops.* " & _

       '     "FROM laptops " & _

       '     "WHERE laptops.operating_sysytem" & operatingsystem & _

       '"AND laptops.manufacturer" & make  ;"
 
      '   strSQL = "SELECT laptops.* " & _

       '  "FROM laptops " & _

       '  "WHERE laptops.[operating_sysytem] = '" & operatingsystem & "' "& _

       '  "AND laptops.[manufacturer]=  '" & make & "' " ;"
 
         qdf.SQL = strSQL

         DoCmd.OpenQuery "Admin_query"

         DoCmd.Close acForm, Me.Name

        ' MsgBox strSQL
 
         Set qdf = Nothing

         Set db = Nothing

         DoCmd.OpenForm "laptop_specs", , "Admin_query
 
        End Sub

Can anyone please point me in the right direction
Regards Panteraboy

May 9 '08 #1

Subscribe Post Reply

1372

panteraboy

Its ok folks I got her sorted. SQL is so fidely for a first timer lol
Here is da code if your Interested

Expand|Select|Wrap|Line Numbers

  
Private Sub CmdSubmit_Click()

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim strSQL As String

Dim operatingsystem As String

Dim make As String

Dim computertype As String
 
Set db = CurrentDb

Set qdf = db.QueryDefs("Admin_query")
 
If (Me.CboOS.Value = "All") Then

     operatingsystem = " Like '*' "
 
Else

 operatingsystem = "='" & Me.CboOS.Value & "' "
 
End If
 
If (Me.CboMake.Value = "All") Then

make = " Like '*' "

Else

make = "='" & Me.CboMake.Value & "' "
 
 End If
 
         strSQL = "SELECT laptops.* " & _

         "FROM laptops " & _

         "WHERE laptops.operating_sysytem" & operatingsystem & _

         "AND laptops.manufacturer" & make & _

         "ORDER BY laptops.model;"
 
         qdf.SQL = strSQL

         'DoCmd.OpenQuery "Admin_query"

         DoCmd.Close acForm, Me.Name

        ' MsgBox strSQL     
 
         Set qdf = Nothing

         Set db = Nothing

         DoCmd.OpenForm "laptop_specs", , "Admin_query"

End Sub

Regards Panteraboy

May 9 '08 #2

Stewart Ross

2,545

Expert Mod 2GB

Hi Panteraboy. There is a mistake in line 32 above, which has an extra double quote and semicolon (no doubt from the first version which you altered). Lines 34-37 repeat part of the definition and should be deleted.

]I also think you have misnamed variables make and operatingsystem, which are really the comparison parts of the WHERE clause. Reading the code first time round it looked like you were missing comparison operators at this point, but this is not the case - it is just that the variable names are very misleading

There is also a mistake in the comparison operator itself

You also appear to have no DIM statement for operatingsystem, and if this is the case you will also not have Option Explicit set. This is a crucial setting, as without it you can use undeclared variables without error - including if you simply make a mistake in a variable name. With Option Explicit on, the compiler will not let you use undeclared variables. It is good practice to have this as a default setting.

Another point: whilst it is correct to use the Value property of a control it does not actually add anything to a direct reference to the control itself, which will return the same thing.

A revised version of your code is listed below.

-Stewart

Expand|Select|Wrap|Line Numbers

 
Option Explicit
 
Private Sub CmdSubmit_Click()
 
Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim strSQL As String

Dim comparison_for_operatingsystem As String

Dim comparison_for_make As String

Dim computertype As String
 
Set db = CurrentDb

Set qdf = db.QueryDefs("Admin_query")
 
If (Me.CboOS = "All") Then

comparison_for_operatingsystem = " Like '*' "

Else

comparison_for_operatingsystem = "='" & Me.CboOS & "' "

End If
 
If (Me.CboMake.Value = "All") Then

comparison_for_make = " Like '*' "

Else

comparison_for_make = "='" & Me.CboMake & "' "

End If
 
strSQL = "SELECT laptops.* " & _

"FROM laptops " & _

"WHERE laptops.operating_sysytem" & comparison_for_operatingsystem & _

" AND laptops.manufacturer " & comparison_for_make
 
qdf.SQL = strSQL

DoCmd.OpenQuery "Admin_query"

DoCmd.Close acForm, Me.Name

' MsgBox strSQL
 
Set qdf = Nothing

Set db = Nothing

DoCmd.OpenForm "laptop_specs", , "Admin_query
 
End Sub

May 9 '08 #3

panteraboy

Thanks steward for that option Explicit tip. I was not aware of that. Your version is much prettier too lol.
Regards Panteraboy

May 9 '08 #4

by: AIM | last post by:

Error in msvc in building inheritance.obj to build hello.pyd Hello, I am trying to build the boost 1.31.0 sample extension hello.cpp. I can not compile the file inheritance.cpp because the two...

Python

Reference-counted error objects

by: Gregory | last post by:

Hi, One of the disadvantages of using error handling with error codes instead of exception handling is that error codes retuned from a function can be forgotten to check thus leading to...

C / C++

Error Handler best practices

by: deko | last post by:

I use this convention frequently: Exit_Here: Exit Sub HandleErr: Select Case Err.Number Case 3163 Resume Next Case 3376 Resume Next

Microsoft Access / VBA

Error crystal websetup 9 install

by: p | last post by:

WE had a Crystal 8 WebApp using vs 2002 which we upgraded to VS2003. I also have Crystal 9 pro on my development machine. The web app runs fine on my dev machine but am having problems deploying....

ASP.NET

error: syntax error before '*' token

by: Manuel | last post by:

I'm trying to compile glut 3.7.6 (dowbloaded from official site)using devc++. So I've imported the glut32.dsp into devc++, included manually some headers, and start to compile. It return a very...

C / C++

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xffc Thread 0x228 DBC 0x437b94 Jet'. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr

by: bazzer | last post by:

hey, im trying to access a microsoft access database from an ASP.NET web application in visual basic 2003.NET. i get the following error when i try running it: Server Error in...

ASP.NET

Linker error

by: developer | last post by:

Hi All I have made a .NET project. the files included are borland c++ files that i am migrate to VC++ .NET I am using Microsoft Visual C++ .NET 2003. the compilation goes through properly,...

.NET Framework

Error: vbc : error BC30464

by: mchuc7719 | last post by:

Hello, I have a Vb.Net 2005 ClassLibrary, when I try to compile using MSBee, only get errors. Before I to run the command line, I open in notepad the .vbproj and I was add the next line: ...

.NET Framework

Custom Error Messages in Access

by: hyperpau | last post by:

Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...

Visual Basic 4 / 5 / 6

Custom Error Messages

by: hyperpau | last post by:

Microsoft Access / VBA

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

Similar topics