Re: Checking for results of SQL statements without saving queries.
From: Amir (agamy_at_actcom.co.il)
Date: 01/22/05
- Next message: leftytx: "writing in english & spanish, are both grammer and spellchecks po."
- Previous message: Jeff Boyce: "Re: Query Only Creent Record?"
- In reply to: Allen Browne: "Re: Checking for results of SQL statements without saving queries."
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 22 Jan 2005 17:24:31 +0200
Thank you very much!!
Kind Regards,
Amir.
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:ORUQujEAFHA.4072@TK2MSFTNGP10.phx.gbl...
> If you are modifying an existing record and perform a DLookup(), it could
> find the record you are currently editing. But a record is not a clash
with
> itself, so we exclude that record by changing the WhereCondition to
exclude
> the primary key value of the current record.
>
> Regarding the date formats, see:
> International Date Formats in Access
> at:
> http://members.iinet.net.au/~allenbrowne/ser-36.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Amir" <agamy@actcom.co.il> wrote in message
> news:eXQh2FCAFHA.2552@TK2MSFTNGP09.phx.gbl...
> > Thank you very much Allen!
> > That was exactly the solution I was looking for.
> >
> > I haven't used the NewRecord line because it's not relevant since I'm
> > using
> > this code for a report making form,
> > but I would be grateful if you could explain why it is relevant in other
> > cases. I don't understand that point.
> >
> > In addition, Why does this solution does not work if I omit the line:
> > Const conJetDate = "\#mm\/dd\/yyyy\#"
> > ?
> > Does a date have to be in it's mm/dd/yyyy format in order to search
> > records
> > in a database?
> >
> > Kind Regards!
> > Amir.
> >
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:eKB1MD6$EHA.2180@TK2MSFTNGP12.phx.gbl...
> >> Use DLookup() to see if there is a clash. This kind of thing:
> >>
> >> Private Sub Form_BeforeUpdate(Cancel As Integer)
> >> Dim strWhere As String
> >> Dim varResult As Variant
> >> Const conJetDate = "\#mm\/dd\/yyyy\#"
> >>
> >> strWhere = "([EventDate] Between " & Format(Me.MinDate, conJetDate)
&
> > _
> >> " And " & Format(Me.MaxDate, conJetDate) & ")"
> >>
> >> If Not Me.NewRecord Then 'Existing record doesn't clash with
itself.
> >> strWhere = strWhere & " AND ([EventID] <> " & Me.EventID & ")"
> >> End If
> >>
> >> varResult = DLookup("EventID", "MyTable", strWhere)
> >> If IsNull(varResult) Then
> >> MsgBox "No clashes."
> >> Else
> >> MsgBox "Clashes with EventID " & varResult
> >> 'Cancel = True
> >> End If
> >> End Sub
> >>
> >> If you want to know how to do it by opening a recordset on a SQL
> >> statement
> >> directly instead of using DLookup(), there's an example that gives the
> > same
> >> results in this article:
> >> http://members.iinet.net.au/~allenbrowne/ser-42.html
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Amir" <agamy@actcom.co.il> wrote in message
> >> news:%23uKTYm5$EHA.2180@TK2MSFTNGP12.phx.gbl...
> >> > Hi!
> >> >
> >> > I have a table with events and dates:
> >> > Field Datatype
> >> > Event Text
> >> > EventDate Date
> >> >
> >> > I have 2 variables which define a range date: MinDate and MaxDate.
> >> >
> >> > I want access to check if there are Events in the range of dates
> > according
> >> > to these variables data,
> >> > and if there is no Event in that specific range of dates, to display
> >> > the
> >> > message: "There are no events in this range of dates."
> >> >
> >> > How can I do that?
> >> >
> >> > I thought about doing something like running a SELECT query like:
> >> > SELECT MyTable.Event, MyTable.EventDate
> >> > FROM MyTable
> >> > WHERE (MyTable.EventDate > MinDate) And (MyTable.EventDate <
MaxDate);
> >> >
> >> > and then make the code to check if the SELECT returns any record, but
I
> >> > don't know how to check if a SELECT query doesn't give any result
using
> >> > code.
> >> >
> >> > In addition, I only know how to do this by saving a query in the
access
> >> > database.
> >> >
> >> > How can I check if an SQL SELECT sentence gives results?
> >> > How can I do this only using a code without saving a query?
> >> >
> >> > Thank you very much!
> >> >
> >> > Regards,
> >> > Amir.
>
>
- Next message: leftytx: "writing in english & spanish, are both grammer and spellchecks po."
- Previous message: Jeff Boyce: "Re: Query Only Creent Record?"
- In reply to: Allen Browne: "Re: Checking for results of SQL statements without saving queries."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
