Listbox Selection

[Asif]

I have a form with a List Box(lst_entry), upon clicking on an entry a
new form (frm_edit) should load up showing the details of the entry
selected in listbox.

In the "On Dbl Click" event I've used the following code;


Private Sub Lst_entry_DblClick(cancel As Integer)
DoCmd.OpenForm "frm_edit", , , "BINPROCESSID = " & lst_entry.Column(0)
&
" and BINPROCESSDate = " & lst_entry.Column(1)
End Sub


However I'm getting the following error message every time I dbl
click
on an item in the list;


Run-time error '3075': Syntax error (missing operator) in query
expression 'BINPROCESSID = 43 and BINPROCESSDate=16/03/07 15:11:06',


Any ideas what I'm doing wrong??


Thanks

 

[Brendan Reynolds]

Answered in another thread in this newsgroup.

 

[Asif]

Thanks Brendan, the form opens but it doesn't pick up the data I've
selected from the listbox!!! Is there something wrong with my coding?

 

[Brendan Reynolds]

Probably the date format. That's why I recommended looking at Allen's
article. It's all there in the article, explained much better than I could
explain it here and now.

 

[Asif]

Probably the date format. That's why I recommended looking at Allen's
article. It's all there in the article, explained much better than I could
explain it here and now.

--
Brendan Reynolds
Access MVP






- Show quoted text -

I've read Allen's article but i don't think thats whats causing the
problem unless i'm mistaken

need more help plz!!

 

[Douglas J. Steele]

I've read Allen's article but i don't think thats whats causing the
problem unless i'm mistaken

need more help plz!!

You're not enclosing your dates in # characters.

 

[RoyVidar]

I've read Allen's article but i don't think thats whats causing the
problem unless i'm mistaken

need more help plz!!

I think there might be several challenges with your initial approach.

One, is that the row source of the listbox probably contains a valid
date/time, which when presented in, and collected from a listbox,
becomes a text. The coersion/conversion, implicit or explicit, between
dates and text, is among the issues creating frustrations when working
with databases. I would recommend trying to avoid such, and only
convert to text when you are sure you wont need to convert it back to a
date again.

The next, as described in Allen Brownes article, that when you "feed" a
date into a string, which is passed to the Jet engine for
interpretation, it needs to be in a format Jet understands. Jet will
understand the format described in the mentioned article, or for
instance the ISO 8601 format "yyyy-mm-dd hh:nn:ss", and it needs the
date enclosed in octothorpes (#) - that is, if the field is defined as
Date/Time.

So, if you are positive the text in the listbox will be consistant, you
could try something like this:

....and BINPROCESSDate = #" & _
Mid$(Trim$(lst_entry.Column(1)), 4, 2) & _
"/" & Mid$(Trim$(lst_entry.Column(1)), 1, 2) & _
"/" & Mid$(Trim$(lst_entry.Column(1)), 7) & "#"

Which - if I've typed reasonable correctly, should become something
like

and BINPROCESSDate=#03/16/07 15:11:06#

i e mm/dd/yy hh:nn:ss

You may test by assigning to a string, then typing to the debug window

dim s as string

s = "BINPROCESSID = " & lst_entry.Column(0) & _
" and BINPROCESSDate = #" & _
Mid$(Trim$(lst_entry.Column(1)), 4, 2) & _
"/" & Mid$(Trim$(lst_entry.Column(1)), 1, 2) & _
"/" & Mid$(Trim$(lst_entry.Column(1)), 7) & "#"
debug.print s

Then hit ctrl+g to pick up the criterion from the immediate pane.

You should be able to copy/paste from there, into the SQL view of a
query based on this table as the WHERE clause (add the keyword WHERE
too), and test whether it is correct or not.

 

[Brendan Reynolds]

Hopefully the advice you've received from others has been enough to resolve
the problem. But if you're still stuck, try posting the expression as it now
stands. We haven't seen any of the changes you've made since you posted the
original expression that didn't include the delimiters, so we're trying to
debug an expression that we haven't seen. If you post the expression as it
now stands, someone will probably be able to see what the problem is.