Assigning a Query to an Array

[LadyDi]

I've just recently learned about array variables, and would like to use one to validate a date that is being entered. I have a query set up to show the dates of all files that have been loaded into the database. Every time a new set of files gets loaded, the user must enter the date for those files. I want to prevent the user from loading files that have already been loaded (the primary key in my table is an autonumber). I would like to set up a loop to compare the date entered to the dates already in the database. If the date is already there, I want an error message to appear. If the date is not there, I want the database to continue as usual. However, I can't figure out how to assign my query (qryDatesPulled) to the array (strDatesPulled (1 to 365)). Do you have any suggestions on how to do this, or a better way to accomplish the same thing?

 

[vbaInet]

Why not just use the DCount() function to see if the date exists in your table?

 

[LadyDi]

How would you go about doing that?

I've tried several ways, and I can't even get it to show me the value of the DCount on my query. This was the last thing that I had: DCount("[Dates Pulled]", "[qryDatesPulled]", [Forms]![frmMain]![FileDate]). Am I typing the DCount right? I want it to check to see if the date entered on the from is already in the query. How can I get it to work?

 

[vbaInet]

You need criteria in the third part of the function. See here for usage:

http://www.techonthenet.com/excel/formulas/dcount.php

 

[jdraw]

see
http://www.techonthenet.com/access/functions/domain/dcount.php

EDIT: OOPS vbaInet has responded while I was typing... at least we had a common message.

 

[LadyDi]

I'm still having some trouble with this one. I tried delcaring a variable and then setting it to the DCount, but it tells me that I have a type mismatch. What am I doing wrong?

Dim strDate As String
Dim LTotal As Long
strDate = InputBox("Enter the date of the report you want to pull.", "File Date", Date - 1)
LTotal = DCount("[Dates Pulled]", "[qryDatesPulled]", "[Dates Pulled]= " & "'" & strDate & "'" & "")

 

[vbaInet]

@jdraw: we sure did.

@LadyDi: [Dates Pulled] is a date field right? You need to surround the criteria with this # instead of single quote.

 

[LadyDi]

That works perfectly. Thank you.

 

[vbaInet]

Glad we could help.