Stop Query

[Diogo]

Public Function Comando001_Click()

Dim mySQL As String

mySQL = "SELECT Numerario.Numerario FROM Numerario WHERE
(((Numerario.Data)=Date()))"

DoCmd.RunSQL mySQL

If mySQL = Null Then

DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery "append_numerario"

End If

End Function

Ok I was trying to use this code to run a query based on another query, but
it stops and says: "run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement"

Could someone help please?

 

[Dirk Goldgar]

In

Public Function Comando001_Click()

Dim mySQL As String

mySQL = "SELECT Numerario.Numerario FROM Numerario WHERE
(((Numerario.Data)=Date()))"

DoCmd.RunSQL mySQL

If mySQL = Null Then

DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery "append_numerario"

End If

End Function

Ok I was trying to use this code to run a query based on another
query, but it stops and says: "run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement"

Could someone help please?

You can only use RunSQL with action queries, such as append, delete, and
make-table queries. You're trying to use it with a select query, to
look up a value. That doesn't work, though the error message is
confusing.

If your intention is to find out if there are any records for today in
the table or query, "Numerario", you *could* open a recordset on your
SELECT statement, but it's probably easier just to use the DLookup
function:

If IsNull(DLookup("Numerario", "Numerario", "[Data]=Date()")) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True
End If

The above code will execute "append_numerario" only if there are no
matching records -- so long as the field Numerario can never have a Null
value.

Another alternative would be to use DCount:

If DCount("*", "Numerario", "[Data]=Date()") = 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True
End If

I prefer not to use SetWarnings, though, so I'd replace this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True

with this:

CurrentDb.Execute "append_numerario"

That's assuming the "append_numerario" is an action query, not a select
query whose output you want to see in a datasheet.

 

[Jim Burke in Novi]

I think Dirk is right, but in your criteria you may need to specify your date
criteria as "[Data] = #" & Date() & "#".

In

Public Function Comando001_Click()

Dim mySQL As String

mySQL = "SELECT Numerario.Numerario FROM Numerario WHERE
(((Numerario.Data)=Date()))"

DoCmd.RunSQL mySQL

If mySQL = Null Then

DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery "append_numerario"

End If

End Function

Ok I was trying to use this code to run a query based on another
query, but it stops and says: "run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement"

Could someone help please?

You can only use RunSQL with action queries, such as append, delete, and
make-table queries. You're trying to use it with a select query, to
look up a value. That doesn't work, though the error message is
confusing.

If your intention is to find out if there are any records for today in
the table or query, "Numerario", you *could* open a recordset on your
SELECT statement, but it's probably easier just to use the DLookup
function:

If IsNull(DLookup("Numerario", "Numerario", "[Data]=Date()")) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True
End If

The above code will execute "append_numerario" only if there are no
matching records -- so long as the field Numerario can never have a Null
value.

Another alternative would be to use DCount:

If DCount("*", "Numerario", "[Data]=Date()") = 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True
End If

I prefer not to use SetWarnings, though, so I'd replace this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True

with this:

CurrentDb.Execute "append_numerario"

That's assuming the "append_numerario" is an action query, not a select
query whose output you want to see in a datasheet.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

And you should explicitly set the format, since Access doesn't respect
Regional Settings in queries:

"[Data] = #" & Format(Date(), "\yyyy\-mm\-dd") & "#"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I think Dirk is right, but in your criteria you may need to specify your
date
criteria as "[Data] = #" & Date() & "#".

In

Public Function Comando001_Click()

Dim mySQL As String

mySQL = "SELECT Numerario.Numerario FROM Numerario WHERE
(((Numerario.Data)=Date()))"

DoCmd.RunSQL mySQL

If mySQL = Null Then

DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery "append_numerario"

End If

End Function

Ok I was trying to use this code to run a query based on another
query, but it stops and says: "run-time error '2342'
A RunSQL action requires an argument consisting of an SQL statement"

Could someone help please?

You can only use RunSQL with action queries, such as append, delete, and
make-table queries. You're trying to use it with a select query, to
look up a value. That doesn't work, though the error message is
confusing.

If your intention is to find out if there are any records for today in
the table or query, "Numerario", you *could* open a recordset on your
SELECT statement, but it's probably easier just to use the DLookup
function:

If IsNull(DLookup("Numerario", "Numerario", "[Data]=Date()")) Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True
End If

The above code will execute "append_numerario" only if there are no
matching records -- so long as the field Numerario can never have a Null
value.

Another alternative would be to use DCount:

If DCount("*", "Numerario", "[Data]=Date()") = 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True
End If

I prefer not to use SetWarnings, though, so I'd replace this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "append_numerario"
DoCmd.SetWarnings True

with this:

CurrentDb.Execute "append_numerario"

That's assuming the "append_numerario" is an action query, not a select
query whose output you want to see in a datasheet.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

In

I think Dirk is right, but in your criteria you may need to specify
your date criteria as "[Data] = #" & Date() & "#".

You don't have to do that if you embed the reference to the Date()
function in the criteria argument itself, as I showed. The DLookup
function *will* work that way. If you want to embed the literal date
value in the criteria string, then you have to use the "#" marks and
format it unambiguously, as you and Doug have pointed out.