WHERE...AND issues

[max1]

Hi,

I just altered a query that was Previously working OK. I now get a data type mismatch error. The error occurs in the query with the 'AND' in the 'WHERE' clause

tblHolesDesignAndSurvey.IDStope data type is number.

Any help much appreciated

Thanks

Sub DisseminateDesignData(STPName As String, sPath As String) 'separates and appends data to correct tables

Dim db As DAO.Database
Dim IDStope As Integer

Dim rst10 As DAO.Recordset
Dim strSQL10 As String


Set db = CurrentDb

        db.Execute "INSERT INTO tblDesignStope (StopeName) VALUES (""" & STPName & """)"

        db.Execute "INSERT INTO tblHolesDesignAndSurvey ( IDStope, VulcanHoleID, DesignOrSurvey) " & _
                   "SELECT tblDesignStope.IDStope, tblTmpHeader.holeid, tblTmpHeader.DesignOrSurvey " & _
                   "FROM tblDesignStope LEFT JOIN tblTmpHeader ON tblDesignStope.StopeName = tblTmpHeader.StopeName " & _
                   "WHERE (((tblDesignStope.StopeName)=""" & STPName & """))"
                   
db.TableDefs.Refresh
Application.RefreshDatabaseWindow

       strSQL10 = "SELECT tblDesignStope.IDStope " & _
                    "FROM tblDesignStope " & _
                    "WHERE (((tblDesignStope.StopeName)=""" & STPName & """))"

        Set rst10 = db.OpenRecordset(strSQL10, dbOpenSnapshot)
        IDStope = rst10(0)

'============PROBLEM HERE - DATA TYPE MISMATCH



        db.Execute "INSERT INTO tblHoleCoordinates ( IDHole, Depth, Azimuth, Inclination, XCoordinate, YCoordinate, ZCoordinate) " & _
                    "SELECT tblHolesDesignAndSurvey.IDHole, tblTmpDesign.depth, tblTmpDesign.azimth, tblTmpDesign.inclin, tblTmpHeader.east, tblTmpHeader.north, tblTmpHeader.level " & _
                    "FROM (tblHolesDesignAndSurvey INNER JOIN tblTmpHeader ON tblHolesDesignAndSurvey.VulcanHoleID = tblTmpHeader.holeid) INNER JOIN tblTmpDesign ON tblHolesDesignAndSurvey.VulcanHoleID = tblTmpDesign.holeid " & _
                    "WHERE (((tblHolesDesignAndSurvey.DesignOrSurvey)= ""DESIGN"")) AND (((tblHolesDesignAndSurvey.IDStope)=""" & IDStope & """))"
                    
db.TableDefs.Refresh
Application.RefreshDatabaseWindow

Set rst10 = Nothing

End Sub

 

[pr2-eugin]

Try,

        db.Execute "INSERT INTO tblHoleCoordinates ( IDHole, Depth, Azimuth, Inclination, XCoordinate, YCoordinate, ZCoordinate) " & _
                    "SELECT tblHolesDesignAndSurvey.IDHole, tblTmpDesign.depth, tblTmpDesign.azimth, tblTmpDesign.inclin, tblTmpHeader.east, tblTmpHeader.north, tblTmpHeader.level " & _
                    "FROM (tblHolesDesignAndSurvey INNER JOIN tblTmpHeader ON tblHolesDesignAndSurvey.VulcanHoleID = tblTmpHeader.holeid) INNER JOIN tblTmpDesign ON tblHolesDesignAndSurvey.VulcanHoleID = tblTmpDesign.holeid " & _
                    "WHERE (((tblHolesDesignAndSurvey.DesignOrSurvey)= 'DESIGN')) AND (((tblHolesDesignAndSurvey.IDStope)=" & IDStope & "))"

 

[max1]

Thanks very much.

all fixed

Quotation marks.....Where clause variables....:banghead:

 

[vbaInet]

Quotation marks.....Where clause variables....:banghead:

If you don't want to be dealing with all these parsing problems you should create queries to perform the INSERTs and UDPATEs and execute the query instead.

 

[namliam]

:banghead: readable code = maintainable code :banghead:

something like:

        db.Execute ....
                    " FROM      (tblHolesDesignAndSurvey " & _ 
                    " INNER JOIN tblTmpHeader             ON tblHolesDesignAndSurvey.VulcanHoleID = tblTmpHeader.holeid)  " & _ 
                    " INNER JOIN tblTmpDesign             ON tblHolesDesignAndSurvey.VulcanHoleID = tblTmpDesign.holeid " & _
                    " WHERE (((tblHolesDesignAndSurvey.DesignOrSurvey)= ""DESIGN""))  " & _ 
                    "   AND (((tblHolesDesignAndSurvey.IDStope       )=""" & IDStope & """))"

And offcourse much more simular stuff

 

[Galaxiom]

readable code = maintainable code

Aside from regular layout as namliam has shown, there are several ways to make SQL code more readable.

Alias the table names to a single character.

Use unique field names in different tables so the table names don't need to be included in the Select clause.

Remove the excess parentheses that Access insists on using in queries.

 

[namliam]

...Snip...

Simular stuff

 

[max1]

Since my code and layout is receiving comments I thought I might put in a few points/questions on your reasoning.

1. I rather have queries in the code so that I know exactly what they are relevant to. The database has tens of queries if i have them as queries in access I will forget the purpose of each and never be able to delete any redundant queries for fear of removing one in use.

2. Yep, I like the suggested layout for the query code

3.Alias table names to a single character? do you mean for the purposes of this forum or within the code that is run? The names used are chosen to help me follow my own code.

4.If two fields in two tables are linked I'd rather used the same field name to make it obvious to myself that they are the same.

5. Alot of these queries start life in the Access query design feature before being moved into the code. If I have problems It is often a matter of trial and error to correct. If you can recommend an online resource to help me learn and avoid the use of the access query design feature that would be great.

 

[Galaxiom]

3.Alias table names to a single character? do you mean for the purposes of this forum or within the code that is run? The names used are chosen to help me follow my own code.

Aliasing shortens the code.

SELECT T.afield, A.whatever
FROM TableWithLongName AS T
INNER JOIN AnotherLongName AS A
ON T.somefield = A.somefield
WHERE T.afield = "xyz"
ORDER BY A.foo

4.If two fields in two tables are linked I'd rather used the same field name to make it obvious to myself that they are the same.

I agree and they need their table name in any reference. However any fields with unique names don't need to have their tablename included.

SELECT table1.ID, uniquefieldname, anotheruniquefieldname
FROM table1
INNER JOIN table2
ON table1.fieldname = table2.fieldname

 

[namliam]

1. To a point I can agree, however a proper naming convention for your objects / queries should help in keeping things cleaned up.

3. While aliassing can help, I prefer to keep an alias to a legible name rather than as short as possible. Removing to much information can hurt your code as well, like removing aliases even before unique column names. While the structure may be known to you, someone inheriting the DB from you may not (yet) be familiar with the structure and therefor not know where the column is from. I prefer to always have a table reference with my columns.
Removing surplus brackets is one thing I ALWAYS do, it really breaks the readability of SQL if there are 20 brackets where you only need 2

4. Fields that contain the same data, customerID offcourse should always be customerID.

5. Query designer, unless you "speak" SQL like I do after much SQL coding, is the prefered way of coding SQL. It is usually faster to click a few times than it is to type 100 chars to (for example) join two tables.

 

[Galaxiom]

Removing surplus brackets is one thing I ALWAYS do, it really breaks the readability of SQL if there are 20 brackets where you only need 2.

Worse still after some edits when there are only 19 brackets remaining.

 

[Brianwarnock]

5. Query designer, unless you "speak" SQL like I do after much SQL coding, is the prefered way of coding SQL. It is usually faster to click a few times than it is to type 100 chars to (for example) join two tables.

And as Pat Hartman once said " you don't get typos with drag and drop"

Brian

 

[Brianwarnock]

Since my code and layout is receiving comments I thought I might put in a few points/questions on your reasoning.

1. I rather have queries in the code so that I know exactly what they are relevant to. The database has tens of queries if i have them as queries in access I will forget the purpose of each and never be able to delete any redundant queries for fear of removing one in use.

It is a while since I used Access and no longer have access to it but I seem to remember that you can document your queries in a comment field where they are listed, or am I dreaming that I did that?

Brian

 

[namliam]

You are not dreaming Brian, you can also (in newer versions) change your view so you can see all related objects together... So tables/queries/forms that depend on eachother are displayed as groups.
Though that doesnt work 100% to my taste it does help a lot.

To each his own though

 

[vbaInet]

1. I rather have queries in the code so that I know exactly what they are relevant to. The database has tens of queries if i have them as queries in access I will forget the purpose of each and never be able to delete any redundant queries for fear of removing one in use.

Like Brian pointed out you can write comments in the Description property of the query, in addition to that you have the Object properties where you can write even more legible comments. Plus you now have the Navigation Pane which allows you to group objects into logical parts.

 

[Brianwarnock]

Are there any performance issues for which ever approach is chosen?

brian

 

[vbaInet]

I was actually thinking of that Brian. Great minds eh!
I think there are performance differences and from memory I think a query wins, but I can test this later and report back.

 

[Brianwarnock]

that will be interesting, although as I no longer work that is all, I assume that a compiled query will outperform the vba code approach but would not put my mortgage on it, if I had one.

Brian

 

[vbaInet]

...if I had one.

You lucky old man

 

[namliam]

Offcourse a stored object will perform faster than an SQL query in VBA.

The difference isnt really that big but... it exists.