Dmin and Dmax With Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thomas [PBD] - 13 May 2008 16:55 GMT

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.

Reply to this Message

Douglas J. Steele - 13 May 2008 17:08 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hello all:
>
[quoted text clipped - 25 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

Thomas [PBD] - 13 May 2008 17:57 GMT

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?

> 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
[quoted text clipped - 48 lines]
> >
> > Any help would be greatly appreciated.

Reply to this Message

Douglas J. Steele - 13 May 2008 19:12 GMT

Create an Append query and run it.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Mr. Steele:
>
[quoted text clipped - 74 lines]
>> >
>> > Any help would be greatly appreciated.

Reply to this Message

Rate this thread: