Checking for results of SQL statements without saving queries.

  • Thread starter Thread starter Amir
  • Start date Start date
A

Amir

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.
 
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
 
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 said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Amir said:
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.
Click to expand...
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Amir said:
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 said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Amir said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much!!

Kind Regards,
Amir.


Allen Browne said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Amir said:
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 said:
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)
Click to expand...
&
_
" 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.

Reply to group, rather than allenbrowne at mvps dot org.

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top