Erratic Behavoir of List Box

  • Thread starter Thread starter marek
  • Start date Start date
M

marek

Hi,

I have a problem with the RowSource propery of a list
box. I have a query string which is based on 2 text boxes
on a form used to specify a date range - its way too long
to post here. It simply inserts the 2 dates entered on
the form in the Between part of my SLQ statement

The results seem very unpredicatable - it includes dates
outside the range entered. If I use the debug.print
command to print out the SQL string, then literally copy
that into a new query SQL window, same results. If I
manually changed the between statement and physically type
in the two dates manually, it works fine - so I guess this
behavoir has something to do with the dates.

Does anyone have any ideas??

Many thanks for taking time to look at this query.

marek
 
marek said:
Hi,

I have a problem with the RowSource propery of a list
box. I have a query string which is based on 2 text boxes
on a form used to specify a date range - its way too long
to post here. It simply inserts the 2 dates entered on
the form in the Between part of my SLQ statement
Click to expand...

How about posting just the WHERE clause? My guess would be that it is
evaluating the BETWEEN as string values instead of dates.
 
That's what it seems it's doing - I have an simple SQL
string to demonstrate this headache:

select tblJobs.DateIn from tblJobs where tblJobs.DateIn
between 01/05/2004 and 17/05/2004

So, when I set the listbox's RowSource property to the
above, no joy. Paste it into a query, still no joy.
Manually edit the Between part and it works fine within
the query. The only difference is the # seperator in
front and after both dates. But even if I ammend the SQL
string to put these in, still no joy. It must be something
to do with the way the dates are being interpreted as you
suggest, but I can't seem to find a way of doing this.

It also seems to mis-interpret the date format to
sometimes read 05/01/2004 instead of 01/05/2004, so it is
definatley the dates that are causing this.
 
I think I have solved it - I use the Format function to
try and force it to recognise it as a date - problem is
though even though query string comes out as 01/05/2004,
when I copy this into a new query SQL window, it reads as
intended - 01/05/2004. But when I switch into the query
design grid it swaps it to 05/01/2004 - very annoying.

However, if i use the Format function and use the format
dd/mmm/yy that seems to solve the problem.

Many thanks for taking the time to look at the problem -
it pointed me in the right direction and no doubt saved
many hours of headaches.
 
Back
Top