Dmin and Dmax With Criteria

  • Thread starter Thread starter Thomas [PBD]
  • Start date Start date
T

Thomas [PBD]

Hello all:

I am having an issue with programming a VBA code to use input boxes, pull
the beginning and ending dates of a month, in order to import a Database
Table. Coding looks right to me, but gives an error stating:

"You cancelled the previous operation" Error 2001 and highlights begwk
criteria.

Sub Append_OH()
Dim month As String
Dim year As String
Dim begwk As String
Dim endwk As String

month = InputBox("Enter Month Name (ex December)", "Enter Month")
year = InputBox("Enter Fiscal Year (ex 2008)", "Enter Year")
begwk = DMin("[Date]", "Week Numbers", "[Month]=" & month & " and [Fiscal
Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]=" & month & " and [Fiscal
Year]=" & year)

DoCmd.TransferDatabase acImport, "Microsoft Access",
"\\Filespk01\cars\future32\overheadnew\databackup.mdb", acTable, "400_" &
begwk & " thru " & endwk, "GWL", False

End Sub

Any help would be greatly appreciated.
 
That very misleading error message usually indicates that you've misspelled
a name, or you've got a type mismatch. In your case, since from the message
in the InputBox message Month is obviously a text field, the latter's
definite. It should be:

begwk = DMin("[Date]", "Week Numbers", "[Month]='" & month & "' and [Fiscal
Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]='" & month & "' and [Fiscal
Year]=" & year)

Exagerated for clarity, that's

begwk = DMin("[Date]", "Week Numbers", "[Month]= ' " & month & " ' and
[Fiscal Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]= ' " & month & " ' and
[Fiscal Year]=" & year)

However, I would strongly urge you to rename both the Month field in your
table and the month and year variables in your code. Month and Year are both
reserved words, and you should never use reserved words for your own
purposes. For a comprehensive list of names to avoid, see what Allen Browne
has at http://www.allenbrowne.com/AppIssueBadWord.html
 
Mr. Steele:

Thank you for the help. You know I tried the " ' " on both Month and Year
at the same time, thinking that it was the problem before I posted in here,
but because YEAR is a number field, it didnt work either (I tossed it out
because I thought it was incorrect).

I however, have a second request.

I now need the table that imported, "400_03/02/2008 thru 03/30/2008" to
append to an existing table "Overhead Data". Is there a VBA coding that will
allow the PasteAppend function?

Douglas J. Steele said:
That very misleading error message usually indicates that you've misspelled
a name, or you've got a type mismatch. In your case, since from the message
in the InputBox message Month is obviously a text field, the latter's
definite. It should be:

begwk = DMin("[Date]", "Week Numbers", "[Month]='" & month & "' and [Fiscal
Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]='" & month & "' and [Fiscal
Year]=" & year)

Exagerated for clarity, that's

begwk = DMin("[Date]", "Week Numbers", "[Month]= ' " & month & " ' and
[Fiscal Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]= ' " & month & " ' and
[Fiscal Year]=" & year)

However, I would strongly urge you to rename both the Month field in your
table and the month and year variables in your code. Month and Year are both
reserved words, and you should never use reserved words for your own
purposes. For a comprehensive list of names to avoid, see what Allen Browne
has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thomas said:
Hello all:

I am having an issue with programming a VBA code to use input boxes, pull
the beginning and ending dates of a month, in order to import a Database
Table. Coding looks right to me, but gives an error stating:

"You cancelled the previous operation" Error 2001 and highlights begwk
criteria.

Sub Append_OH()
Dim month As String
Dim year As String
Dim begwk As String
Dim endwk As String

month = InputBox("Enter Month Name (ex December)", "Enter Month")
year = InputBox("Enter Fiscal Year (ex 2008)", "Enter Year")
begwk = DMin("[Date]", "Week Numbers", "[Month]=" & month & " and [Fiscal
Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]=" & month & " and [Fiscal
Year]=" & year)

DoCmd.TransferDatabase acImport, "Microsoft Access",
"\\Filespk01\cars\future32\overheadnew\databackup.mdb", acTable, "400_" &
begwk & " thru " & endwk, "GWL", False

End Sub

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Create an Append query and run it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thomas said:
Mr. Steele:

Thank you for the help. You know I tried the " ' " on both Month and Year
at the same time, thinking that it was the problem before I posted in
here,
but because YEAR is a number field, it didnt work either (I tossed it out
because I thought it was incorrect).

I however, have a second request.

I now need the table that imported, "400_03/02/2008 thru 03/30/2008" to
append to an existing table "Overhead Data". Is there a VBA coding that
will
allow the PasteAppend function?

Douglas J. Steele said:
That very misleading error message usually indicates that you've
misspelled
a name, or you've got a type mismatch. In your case, since from the
message
in the InputBox message Month is obviously a text field, the latter's
definite. It should be:

begwk = DMin("[Date]", "Week Numbers", "[Month]='" & month & "' and
[Fiscal
Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]='" & month & "' and
[Fiscal
Year]=" & year)

Exagerated for clarity, that's

begwk = DMin("[Date]", "Week Numbers", "[Month]= ' " & month & " ' and
[Fiscal Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]= ' " & month & " ' and
[Fiscal Year]=" & year)

However, I would strongly urge you to rename both the Month field in your
table and the month and year variables in your code. Month and Year are
both
reserved words, and you should never use reserved words for your own
purposes. For a comprehensive list of names to avoid, see what Allen
Browne
has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thomas said:
Hello all:

I am having an issue with programming a VBA code to use input boxes,
pull
the beginning and ending dates of a month, in order to import a
Database
Table. Coding looks right to me, but gives an error stating:

"You cancelled the previous operation" Error 2001 and highlights begwk
criteria.

Sub Append_OH()
Dim month As String
Dim year As String
Dim begwk As String
Dim endwk As String

month = InputBox("Enter Month Name (ex December)", "Enter Month")
year = InputBox("Enter Fiscal Year (ex 2008)", "Enter Year")
begwk = DMin("[Date]", "Week Numbers", "[Month]=" & month & " and
[Fiscal
Year]=" & year)
endwk = DMax("[Date]", "Week Numbers", "[Month]=" & month & " and
[Fiscal
Year]=" & year)

DoCmd.TransferDatabase acImport, "Microsoft Access",
"\\Filespk01\cars\future32\overheadnew\databackup.mdb", acTable, "400_"
&
begwk & " thru " & endwk, "GWL", False

End Sub

Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top