IF statement and tables in VBA

  • Thread starter Thread starter Andrew
  • Start date Start date
A

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

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

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
 
-----Original Message-----


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

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

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

(please reply to the newsgroup)


.
Click to expand...

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.
 
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
 
Andrew said:
-----Original Message-----


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

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

.
Click to expand...

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

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

How can I run this module from a macro?
 
Andrew said:
-----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
.
Click to expand...

How can I run this module from a macro?
Click to expand...

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.
 
Back
Top