Re: date useage in vb expression

From: tina (nospam_at_address.com)
Date: 05/28/04 

Date: Fri, 28 May 2004 01:42:07 GMT

okay, now i get why you were putting that comma in the code originally. and
the syntax you used exactly matched the syntax in the Help topic, for using
a WHERE clause rather than a named filter or query.
i've assumed your scenario was this: you have a form bound to a query, and
when you open the form, all the records appear. you have an unbound combo
box on *that same form*, and when you choose a date from the droplist, you
want to filter the records *on that same open form* to see only the record
with the matching date.
if the above is correct, then according to the Help example, the filter
action - with that initial comma - should work. but since it doesn't, here's
a couple alternative codes to try:

    DoCmd.ApplyFilter ,"[call_date] = #" & Me!Combo14 & "#"

or

    DoCmd.ApplyFilter ,"[call_date] = #" & CDate(Me!Combo14) & "#"

or

    Me.Filter = "[call_date] = #" & Me!Combo14 & "#"
'if the above line doesn't work, try the CDate(...) variation here, as well.
    Me.FilterOn = True

if none of the code options above work, suggest you try using the CDate()
function on the date field in the combo box's RowSource. then test the code
options again.
btw, leave out the "Dim rs As Object" line altogether. if you posted the
entire code for the AfterUpdate event, then that line is doing nothing for
you at all - since rs is never used in the code.

hth

"Microsoft" <scottb@cecomet.net> wrote in message
news:%23ri2%23UDREHA.3732@TK2MSFTNGP11.phx.gbl...
> thnak you Tina & Ken. The comma sems to be the trick to get the form to
> open. But, it returns all the records. here is the code I have used
inthe
> text field forms.
>
> Private Sub Combo14_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
> DoCmd.ApplyFilter "call_date = #" & Combo14 & "#"
>
> End Sub
>
> Private Sub Form_Open(Cancel As Integer)
> ' Find the record that matches the control.
> Dim rs As Object
> DoCmd.ApplyFilter "call_date = #" & Combo14 & "#"
>
> End Sub
>
> The combo box is unbound. The form is bound to a query that returns the
> correct data. I currently have 6 records, all with different dates, so I
> should only get one record per dae choice. But instead
> I get all 8 records no matter what date I choose from the combo box. Can
> this be done with a date?
>
> Best regards
> Scott B
>
> "Ken Snell" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
> news:%23iJfI65QEHA.3616@TK2MSFTNGP09.phx.gbl...
> > I believe you need to delete the comma after "Apply Filter".
> >
> > DoCmd.ApplyFilter "call_date = #" & Combo14 & "#"
> >
> >
> > --
> > Ken Snell
> > <MS ACCESS MVP>
> >
> > "Microsoft" <scottb@cecomet.net> wrote in message
> > news:eAKAyE5QEHA.3348@TK2MSFTNGP09.phx.gbl...
> > > Tina,
> > >
> > > I still get the same error, I get a debug dialog box and it highlights
> the
> > > same line as before. Any thoughts?
> > >
> > > Best regards,
> > > Scott B
> > >
> > >
> > > "tina" <nospam@address.com> wrote in message
> > > news:hRbtc.74080$hH.1342706@bgtnsc04-news.ops.worldnet.att.net...
> > > > try
> > > >
> > > > DoCmd.ApplyFilter , "call_date = #" & Combo14 & "#"
> > > >
> > > > hth
> > > >
> > > >
> > > > "Microsoft" <scottb@cecomet.net> wrote in message
> > > > news:evgTky4QEHA.1644@TK2MSFTNGP09.phx.gbl...
> > > > >
> > > > > I have an unbound combo box that uses the following code to choose
a
> > > > > particular type of information from a from. I have used it
several
> > > times
> > > > > for text fields, but now I need to use it for a date field. Can
> > anyone
> > > > help
> > > > > me rewrite it for a date field?
> > > > >
> > > > > DoCmd.ApplyFilter , "call_date = '" & Combo14 & "'"
> > > > >
> > > > > Best regards,
> > > > > Scott B
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Can I use a formula in place of OffsetRows?
    ... Private Sub Worksheet_Change ... Dim DestCell As Range ... I have toyed with the code but I can never figure VBA out. ... That solved the syntax issue. ...
    (microsoft.public.excel.misc)
  • Re: about ADO vb and semi colon
    ... I would like import a text file with delimited semi-colon but not with comma ... Dim cn As New ADODB.Connection ... Private Sub CmdInsert_Click ... objTable.Columns.Append "F1", adWChar ...
    (microsoft.public.vb.database.ado)
  • Re: ADO and semi-colon
    ... I would like import a text file with delimited semi-colon but not with comma ... Dim cn As New ADODB.Connection ... Private Sub CmdInsert_Click ... objTable.Columns.Append "F1", adWChar ...
    (comp.lang.basic.visual.misc)
  • import into access with vb6
    ... I would like import a text file with delimited semi-colon but not with comma ... Dim cn As New ADODB.Connection ... Private Sub CmdInsert_Click ... objTable.Columns.Append "F1", adWChar ...
    (comp.lang.basic.visual.misc)
  • RE: List box Myarray to long
    ... Just leave out the comma in the start of the new line. ... Private Sub cmdCancel_Click ... Dim StrSkillList As String ... Dim var, var2, var3 ...
    (microsoft.public.word.vba.general)
Loading