SQL HELP 1

[Anthony904]

I have an access 97 SQL on a form.

When I tried to add a WHERE statement to the end of the code I get an error '3075'

Here is the SQL:

If sqlString <> "" Then sqlString = " Where" & sqlString
sqlString = "SELECT TlbSchedulingData.Site, TlbSchedulingData.SiteCode, TlbSchedulingData.DateRequested, TlbSchedulingData.Units, TlbSchedulingData.UnitOfMeasure, TlbSchedulingData.NumberOfContainers, TblSiteCodeDisposers.Container, TlbSchedulingData.DisposalLocation, TblSiteCodeDisposers.PricePerUnit FROM TblSiteCodes INNER JOIN (TblSiteCodeDisposers INNER JOIN TlbSchedulingData ON (TblSiteCodeDisposers.DisposalLocation = TlbSchedulingData.DisposalLocation) AND (TblSiteCodeDisposers.SiteCode = TlbSchedulingData.SiteCode) AND (TblSiteCodeDisposers.Site = TlbSchedulingData.Site)) ON (TblSiteCodes.SiteCode = TblSiteCodeDisposers.SiteCode) AND (TblSiteCodes.Site = TblSiteCodeDisposers.Site) WHERE TlbSchedulingData.BillingVerified=True" & sqlString & ";"

The Bold section is the part I added..

THANKS!

 

[SkipVought]

Hi,

[red]This[/red] makes no sense...

WHERE TlbSchedulingData.BillingVerified=True" [b][red]& sqlString[/red][/b] & ";"

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Anthony904]

Heres the entire code..

Dim sqlString As String
sqlString = ""

If Not (IsNull(Me.txtStartOpen) Or IsNull(Me.txtStartEnd)) Then
sqlString = " TlbSchedulingData.DateRequested Between #" & Me.txtStartOpen & "# And #" & Me.txtStartEnd & "# "
End If
If Not (IsNull(Me.txtDisposalLocation)) Then
If sqlString <> "" Then sqlString = sqlString & " And"
sqlString = sqlString & " TlbSchedulingData.DisposalLocation Like '" & Me.txtDisposalLocation & "' "
End If

If sqlString <> "" Then sqlString = " Where" & sqlString
sqlString = "SELECT TlbSchedulingData.Site, TlbSchedulingData.SiteCode, TlbSchedulingData.DateRequested, TlbSchedulingData.Units, TlbSchedulingData.UnitOfMeasure, TlbSchedulingData.NumberOfContainers, TblSiteCodeDisposers.Container, TlbSchedulingData.DisposalLocation, TblSiteCodeDisposers.PricePerUnit FROM TblSiteCodes INNER JOIN (TblSiteCodeDisposers INNER JOIN TlbSchedulingData ON (TblSiteCodeDisposers.DisposalLocation = TlbSchedulingData.DisposalLocation) AND (TblSiteCodeDisposers.SiteCode = TlbSchedulingData.SiteCode) AND (TblSiteCodeDisposers.Site = TlbSchedulingData.Site)) ON (TblSiteCodes.SiteCode = TblSiteCodeDisposers.SiteCode) AND (TblSiteCodes.Site = TblSiteCodeDisposers.Site) WHERE TlbSchedulingData.BillingVerified=True" & sqlString & ";"

Debug.Print sqlString
CurrentDb.QueryDefs.Delete "qryTest"
CurrentDb.CreateQueryDef "qryTest", sqlString

Don't know if that makes more sense..

 

[SkipVought]

What string do you get from Debug.Print sqlString?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Anthony904]

well to give you an overview of what I'm looking for..

I have a form that queries through a table for the specific dates and the disposal locations..

I have the task of modifying this form to add another field BillingVerfied which displays only the records that are checked.

I believe that the person who wrote this last, tried to retrieve data from table TlbSchedulingData and display that to a query qryTest.

Now as far as the question you had for about Debug.Print SqlString.. I did a msgbox of it.. and it displayed the SQL.. I'm not sure why..

Thanks!

 

[PJStephenson]

You have managed to get two WHERE clauses into your SQL string.
The first one is set in your IF statements and if the criteria is not blank becomes the WHERE statement on your SQL String.

Try removing your new text and putting another if statement to build up your where criteria.

something like:

If sqlString <> "" Then 
sqlString = sqlString & " And"
end if
sqlString = sqlString & " TlbSchedulingData.BillingVerified=True"

[&eta;][&beta;][&pi;]

 

[Anthony904]

Yes.. I just thought of that...

thanks for the help!