Between two dates

[TommyF]

I am trying to bring up in a list box all the records that have yes in the onstop record and with the date between 2 certain dates with the following code

Dim wh As Date
Dim da As Date
wh = Format(DateAdd("d", -3, Date), "dd-mm-yyyy")
da = Format(Date, "dd-mm-yyyy")

Me.List39.RowSource = _
"SELECT DISTINCT Name, dt FROM Customers " & _
"WHERE onstop = yes AND dt between #" & wh & "# AND #" & da & "#"

It does not seem to work as it should as it seems to bring up dates before wh by over a month.

If I change the wh and da for actual dates then it works ok.

Any Ideas

 

[RoyVidar]

Hi!

If you have regional settings different from the US settings, then you need to do the formatting either to string variables, or if date variables do the formatting directly into the sql string (the format you're using, which incidently is rather close to what I use, creates challenges for jet):

[tt]...AND dt between #" & format$(wh, "yyyy-mm-dd") & ....[/tt]

Here using ansi format, which is supposed to work on all databases.

Roy-Vidar

 

[TommyF]

Thanks for that RoyVidar I have been pulling my hair out for hours with that.

Thanks again

 

[TommyF]

I have changed my SQL from the above and it know includes a time field (ti) as follows

"SELECT DISTINCT Account, Name, dt, ti, amount, mon, comm FROM Customers " & _
"WHERE onstop = yes AND dt between #" & Format(wh, "yyyy-mm-dd") & "# AND #" & Format(Date, "yyyy-mm-dd") & "#" & _
"ORDER BY dt, ti DESC"

As you can see I am trying to sort the list by the date and then the time but I believe that because the ti is not formated in the correct way then it does not work properly

I have tried

"ORDER BY dt, FORMAT(ti,"Long Time") DESC" but it does not seem to like the " marks around the Long Time.

Can anybody tell me how to format my time filed in an SQL statement?

 

[RoyVidar]

Inside the string, use format(somedate,'long time') - i e single quote.

Please also state more than "does not work properly", it's very hard to help with such description.

That said, here you are sorting first on the date Ascending and then on time Descending, doesn't it sort that way?

Roy-Vidar

 

[TommyF]

Thanks for the help again.

Sorry RoyVidar I should have put more information about why it does not work but you have sorted me out anyway. What I was trying to do was to sort both columns DESC and I did not realise that you had to put DESC after each item.


Thanks again

 

[TommyF]

I promise this will be the last question on this. I have got my code working as follows so thanks for all your help.

Dim wh As Date
Dim da As Date
wh = DateAdd("d", -3, Date)


Me.List39.RowSource = _
"SELECT DISTINCT Account, Name, dt, ti, amount, mon, comm FROM Customers " & _
"WHERE onstop = yes AND dt between #" & Format(wh, "yyyy-mm-dd") & "# AND #" & Format(Date, "yyyy-mm-dd") & "#" & _
"ORDER BY dt DESC, ti DESC"

What happens is this code fills a list box when the form opens and I have a timer event that does a requery on the list to check for any updates.

This all works fine in Access 2k but I have converted it back to Access 97 and for some reason the code does not list anything in the list box.

I have compiled all the code and it compiles ok, I have ckecked that I have all the same referances in the VB code.

It seems to refresh but the list box stays empty.

Any Ideas

 

[RoyVidar]

Works on my setup.

Try doing a

[tt]Debug.Print "SELECT DISTINCT Account, Name, dt, ti, amount, mon, comm FROM Customers " & _
"WHERE onstop = yes AND dt between #" & Format(wh, "yyyy-mm-dd") & "# AND #" & Format(Date, "yyyy-mm-dd") & "# " & _
"ORDER BY dt DESC, ti DESC"[/tt]

in stead of assigning. Then (CTRL+G) copy it to the SQL view of the QBE and see if you can run it there (there's a missing space after the last hash (#), but I don't think thats doing this).

Check also that the properties of the listbox is the same as in 2000, might perhaps assign the rowsourcetype too?

[tt]Me!list39.RowSourceType ="Table/Query"[/tt]

- else I don't know....

Roy-Vidar

 

[TommyF]

Thanks for all your help RoyVider.

I have managed to sort this. I had to take the words DISTINCT out of the code and it works fine.