Need VBA Help

[Becky Isserman]

Let me explain what I am trying to do. I want a dlookup to find all
the fdids that did not report 3 months back when someone pushes a
button. Then I want those fdids to be used to insert a record into the
"Logs" table with "Not Reporting", Month(Not Reported), Year(Not
Reported), and Letter Submitted (Checked). Then I want a report to
open up that will send a letter to everyone not reporting. I have very
few hours left at this client and would like to get it done asap.

Here is my code:
Private Sub Command33_Click()
Dim FDID As String
FDID = (DLookup("[FDID]", "Logged", "Report Year = Year(Date()-92)" And
"Month = MonthName(Month(Date()-92))"))
DoCMD.RunSQL "INSERT INTO Logged ( FDID, [Report Year], [Month], [Type
of Media], [Letter Sent] )
SELECT Logged.FDID, Logged.[Report Year], Logged.Month, Logged.[Type of
Media], Logged.[Letter Sent]
FROM Logged
WHERE (((Logged.FDID)="FDID") AND ((Logged.[Report
Year])<Year(Date()-92)) AND ((Logged.Month)<Month(Date()-92)) AND
((Logged.[Letter Sent])=Yes))";
DoCmd.OpenReport "Delinquent Letter"
End Sub

It is giving me an error in the first statement related to the Report
Year and the Month. It says that they are incorrect type I believe. The
Report Year and Month are text fields. I need to do a dlookup of all
fdids that did not report 3 months ago when someone pushes a button. I
am not exactly sure what the correct syntax is.
Also, the insert function will not work it is throwing off a 3134 run
time error, which I googled and looked on technet for and could not
find answers. Any help would be appreciated at
(e-mail address removed) or MSN as (e-mail address removed).
Reply With Quote

 

[Douglas J. Steele]

You've got a field name with a space in it: you need to enclose the field
name in square brackets ([Report Year]).

You've got a field name that's a reserved word (Month). If you cannot (or
will not) rename the field, enclose it in square brackets ([Month])

If Report Year and Month (which I'll call Report Month, even though I
personally feel field names with spaces in them are a very bad mistake) are
text, you need to enclose the values in quotes:

FDID = DLookup("[FDID]", "Logged", _
"[Report Year] = '" & Year(Date()-92) & _
"' And [Report Month] = '" & MonthName(Month(Date()-92)) & "'")

Exagerated for clarity, that's

FDID = DLookup("[FDID]", "Logged", _
"[Report Year] = ' " & Year(Date()-92) & _
" ' And [Report Month] = ' " & MonthName(Month(Date()-92)) & " ' ")

Your 3134 error ("Syntax error in INSERT INTO statement.") is caused by
roughly the same thing.

DoCMD.RunSQL "INSERT INTO Logged ( FDID, [Report Year], " & _
"[Month], [Type of Media], [Letter Sent] ) " & _
"SELECT Logged.FDID, Logged.[Report Year], Logged.[Report Month], " & _
"Logged.[Type of Media], Logged.[Letter Sent] FROM Logged " & _
"WHERE Logged.FDID='" & FDID & "' AND " & _
"Logged.[Report Year] < '" & Year(Date()-92) & "' AND " & _
"Logged.[Report Month] < '" & Month(Date()-92)) & "' AND " & _
"Logged.[Letter Sent]=True"

I'm assuming that you actually wanted to compare the value of Logged.FDID to
the value of FDID you looked up using the DLookup statement (your code was
looking for the literal characters FDID).

Now, you've got a real inconsistency there: in one place, you're comparing
[Report Month] to the value of the MonthName function (which will be
January, February, etc.), whereas in the other, you're comparing it to the
value of the Month function (which will be 1, 2, 3, etc.). Also, if [Report
Month] is the month number, not its name, are you store 01, 02, 03. ... or
1, 2, 3? In the second case, you'll find 10, 11 and 12 will all be less than
2, 3, 4,... , 9.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Let me explain what I am trying to do. I want a dlookup to find all
the fdids that did not report 3 months back when someone pushes a
button. Then I want those fdids to be used to insert a record into the
"Logs" table with "Not Reporting", Month(Not Reported), Year(Not
Reported), and Letter Submitted (Checked). Then I want a report to
open up that will send a letter to everyone not reporting. I have very
few hours left at this client and would like to get it done asap.

Here is my code:
Private Sub Command33_Click()
Dim FDID As String
FDID = (DLookup("[FDID]", "Logged", "Report Year = Year(Date()-92)" And
"Month = MonthName(Month(Date()-92))"))
DoCMD.RunSQL "INSERT INTO Logged ( FDID, [Report Year], [Month], [Type
of Media], [Letter Sent] )
SELECT Logged.FDID, Logged.[Report Year], Logged.Month, Logged.[Type of
Media], Logged.[Letter Sent]
FROM Logged
WHERE (((Logged.FDID)="FDID") AND ((Logged.[Report
Year])<Year(Date()-92)) AND ((Logged.Month)<Month(Date()-92)) AND
((Logged.[Letter Sent])=Yes))";
DoCmd.OpenReport "Delinquent Letter"
End Sub

It is giving me an error in the first statement related to the Report
Year and the Month. It says that they are incorrect type I believe. The
Report Year and Month are text fields. I need to do a dlookup of all
fdids that did not report 3 months ago when someone pushes a button. I
am not exactly sure what the correct syntax is.
Also, the insert function will not work it is throwing off a 3134 run
time error, which I googled and looked on technet for and could not
find answers. Any help would be appreciated at
(e-mail address removed) or MSN as (e-mail address removed).
Reply With Quote