IF statement and tables in VBA

[Andrew]

In VB what is the syntax for an IF statement where I want
to compare two values in a table?

If Table1.Column1 = Table2.Column2 Then
statement
End If

Please correct my syntax.

 

[Dirk Goldgar]

In VB what is the syntax for an IF statement where I want
to compare two values in a table?

If Table1.Column1 = Table2.Column2 Then
statement
End If

Please correct my syntax.

I'm afraid there's a bit more to it than that. Tables potentially
contain many records; which record in Table1 do you want to compare to
which record in Table2? You can't compare tables, records, or fields
using anything like the sort of syntax you propose. I suppose you might
use the DLookup function to pull values from each table and compare
them, so long as you can identify which record is wanted in each case,
or can be confident there's only one record in each. But really you
have to explain what it is you're after before any "correct" answer can
be given.

 

[Dirk Goldgar]

In Access I want a macro to run only if the 'current
trading day' is the 'first trading day of the month'. I
have two queries that work out the 'current trading day'
and the 'first trading day of the month'. These two
queries only return one row. The date format is 20030701
and is text.

I would like to design a module to do this but I don't
know how. Could you help

For that you could use the DLookup function. Suppose your queries are
named "qryFirstTradingDayOfMonth" and "qryCurrentTradingDay", and they
return fields "FirstTradingDayOfMonth" and "CurrentTradingDay",
respectively. Then you could compare their results with:

If DLookup("CurrentTradingDay", "qryCurrentTradingDay") = _
DLookup("FirstTradingDayOfMonth", "qryFirstTradingDayOfMonth") _
Then
' run the macro
Else
' don't.
End If

 

[Andrew]

-----Original Message-----


For that you could use the DLookup function. Suppose your queries are
named "qryFirstTradingDayOfMonth"

and "qryCurrentTradingDay", and they

return fields "FirstTradingDayOfMonth" and "CurrentTradingDay",
respectively. Then you could compare their results with:

If DLookup

("CurrentTradingDay", "qryCurrentTradingDay") = _

DLookup

("FirstTradingDayOfMonth", "qryFirstTradingDayOfMonth") _

Then
' run the macro
Else
' don't.
End If

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

(please reply to the newsgroup)


.

This is what I have done so far:

Public Sub StartOfMonth()
Dim stDocName As String

If DLookup([Last Trading Day], [Qry_Dates_upto_LTD]) = _
DLookup([First of Month],
[Qry_First_of_the_Month]) _
Then
DoCmd.RunMacro "Macro1"
End If


End Sub
________________________________

I get a Complie error: External name not defined.
[Last Trading Day] is highlighted.

 

[Andrew]

Thanks so much for your help I worked it out.

Public Sub StartOfMonth()
Dim stDocName As String

If DLookup("[Last Trading Day]", "[Qry_Dates_upto_LTD]") =
_
DLookup("[First of
Month]", "[Qry_First_of_the_Month]") _
Then
DoCmd.RunMacro "Macro1"
End If


End Sub

 

[Dirk Goldgar]

-----Original Message-----


For that you could use the DLookup function. Suppose your queries
are named "qryFirstTradingDayOfMonth"

and "qryCurrentTradingDay", and they

return fields "FirstTradingDayOfMonth" and "CurrentTradingDay",
respectively. Then you could compare their results with:

If DLookup

("CurrentTradingDay", "qryCurrentTradingDay") = _

DLookup

("FirstTradingDayOfMonth", "qryFirstTradingDayOfMonth") _

Then
' run the macro
Else
' don't.
End If

.

This is what I have done so far:

Public Sub StartOfMonth()
Dim stDocName As String

If DLookup([Last Trading Day], [Qry_Dates_upto_LTD]) = _
DLookup([First of Month],
[Qry_First_of_the_Month]) _
Then
DoCmd.RunMacro "Macro1"
End If


End Sub
________________________________

I get a Complie error: External name not defined.
[Last Trading Day] is highlighted.

The field and table names must be enclosed in quotes. Try this version:

If DLookup("Last Trading Day", "Qry_Dates_upto_LTD") = _
DLookup("First of Month", "Qry_First_of_the_Month") _
Then
DoCmd.RunMacro "Macro1"
End If

 

[Andrew]

-----Original Message-----
Thanks so much for your help I worked it out.

Public Sub StartOfMonth()
Dim stDocName As String

If DLookup("[Last Trading Day]", "[Qry_Dates_upto_LTD]") =
_
DLookup("[First of
Month]", "[Qry_First_of_the_Month]") _
Then
DoCmd.RunMacro "Macro1"
End If


End Sub
.

How can I run this module from a macro?

 

[Dirk Goldgar]

-----Original Message-----
Thanks so much for your help I worked it out.

Public Sub StartOfMonth()
Dim stDocName As String

If DLookup("[Last Trading Day]", "[Qry_Dates_upto_LTD]") =
_
DLookup("[First of
Month]", "[Qry_First_of_the_Month]") _
Then
DoCmd.RunMacro "Macro1"
End If


End Sub
.

How can I run this module from a macro?

It's not a module; it's a Sub. A module is a container for procedures
(Subs and Functions) and variables, but shouldn't be confused with the
things it contains.

Anyway, to run that procedure from a macro you'll have to change it from
a Sub to a Function. Just change the keyword "Sub" to "Function" on the
first and last lines. Then you can use the RunCode macro action to
execute it.

I think it would be better in the long run for you to get comfortable
enough with VBA code that you stop using macros entirely. Your
applications will be the better for it.