VBA Query problem 3

[mo2783]

I have two tables, one is called Sample and the other Results. With a relationship of one to many. (One sample can have many results)

What I am trying to do is select a date and display the results of the test for the next 14 days. (Eg Say a user selects 10/10/2002 then the next 14 days will be shown with the results.)

Anyway I have written the following code to find the date from the tables selected, if the results have been gather for that date and then get the next day. If no results are found for that date then ignore the date and move on to the next date.

Public Sub CollectdateSampleValues(ByVal txtDateRcvd As Date)
Dim I As Integer
Dim rsTmp, rsDateAndListerine As DAO.Recordset
Dim strSQL As String
Dim CountOfOccurances As Integer
Dim CountOfICert As Integer

strSQL = "SELECT COUNT(*) AS FOUND FROM " & " Results RIGHT JOIN Sample ON Results.ISmpN = Sample.ISmpN " & _
"WHERE (((Sample.ISmpDateRcvd)= " & txtDateRcvd & ") AND ((Results.ICertCertifDesc) Like ""listeria* "")); "

Set rsTmp = CurrentDb.OpenRecordset(strSQL)

If rsTmp.Fields(0).Value > 0 Then
rsTmp.Close

End If

End Sub

When I ran the program and reach the “if “statement it shows no records have been found although I know for a fact that the date selected has 48 results. So the “if” statement should me greater then 0 and carry, not exit. Can someone help please.

 

[SkipVought]

Hi,

   "WHERE (((Sample.ISmpDateRcvd)= #" & txtDateRcvd & "#) AND ((Results.ICertCertifDesc) Like ""listeria* "")); "

#...# converts a date string to a Date Value.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[SuryaF]

I'd suggest to format the date as "dd-mmm-yyyy" before concatenation. This way you'll have no confusion between the month and the day.

   "WHERE (((Sample.ISmpDateRcvd)= #" & Format(txtDateRcvd,"dd-mmm-yyyy") & "#) AND ((Results.ICertCertifDesc) Like ""listeria* "")); "

 

[mo2783]

Thanks for the Solution, it works i all i did was add the "#"

"WHERE (((Sample.ISmpDateRcvd)=#" & txtDateRcvd & "#) AND ((Results.ICertCertifDesc) Like ""listeria*"")); "

Mo

 

[MajP]

Here is one that will keep you out of trouble. Very important to always format the date (as Suryaf said) in Sql so Jet knows how to interpret it. So if you use a lot of dates in sql:

Function SQLDate(varDate As Variant) As String
If IsDate(varDate) Then
SQLDate = "#" & Format$(varDate, "yyyy-mm-dd") & "#"
End If
End Function

To use

"WHERE (((Sample.ISmpDateRcvd)=" & SQLDate(txtDateRcvd) & ") AND ((Results.ICertCertifDesc) Like ""listeria*""));

 

[SkipVought]

BTW,

If your variable is a real date then you don't need any conversion...

   "WHERE (((Sample.ISmpDateRcvd)= " & DateRcvd & ") AND ((Results.ICertCertifDesc) Like ""listeria* "")); "

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[MajP]

Skip,

If your variable is a real date then you don't need any conversion...

True if everyone is working off the same regional settings. I find so many people on Tek-Tips are outside the US that every solution I post I will convert. Often I would post code with sql dates, and people would tell me it is not working.

 

[SuryaF]

I agree with MajP, regional settings can screw everything up.
You can try it out. Change your regional settings and put the dates on dd/mm/yy then try to build a string with simple concatenation.
Better safe than sorry, it's very easy with MajP's function.

 

[PHV]

Skip, in JetSQL date constants should be enclosed by #

Another note: testing Sample.ISmpDateRcvd and Results.ICertCertifDesc defeats the OUTER join.

strSQL = "SELECT Count(*) FOUND FROM Results R INNER JOIN Sample S ON R.ISmpN=S.ISmpN " & _
"WHERE S.ISmpDateRcvd=#" & Format(txtDateRcvd, "yyyy-mm-dd") & "# AND R.ICertCertifDesc Like 'listeria*'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886