Help with Dates ( Comparison)

[ortega3eduardo]

Hello,
Can anyone please help with my situation? Here is the scenario: I am trying to retrieve the dates from two dates I have in two separate text boxes. For example:

Textbox1 = Date 1 ( example 6/01/2003)
Textbox2 = Date 2 ( example 6/05/2003)

I need a way to code this in Visual Basic to retrieve all days inbetween. My result should be something like:

06/01/2003, 06/02/2003, 06/03/2003, 06/04/2003, 06/05/2003

Does anyone know how to retrieve this information from two dates which I have in two separate text boxes?

Your help would be greatly appreciated. Thanks

 

[psemianonymous]

You can use the greater-than/less-than operators:

MyDate >= txtStartDate.Value AND MyDate <=txtEndDate.Value


or the BETWEEN ## AND ## trinary operator:


MyDate BETWEEN txtStartDate.Value AND txtEndDate.Value



...it helps if you tell us what you're going to DO with the dates once you have them. Run a query? Open a worksheet? Print a report? Make a banana shake? No one knows.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[ortega3eduardo]

Thanks for your help. This is the first time I have done this here.
I am trying to obtain a list of dates between the two dates specified. Once this information is obtained I want to compare it against a table in Access Database which contains customer information. If there is any activity from that customer on a certain date in that table that matches the dates in my vb list, then give me the name of that customer. I realize there might be a more efficient way of doing this but I haven't thought of anything yet.

Any suggestions?

Using the BETWEEN statement you specified, I get an Compile error stating Expected end of statement.
I apologize. I am new to vb. I know I still have a lot to learn. Thanks for you help.

 

[psemianonymous]

Yes, there are several good ways to do this in Access. There is a problem though: consider that you may have *more* than one customer listed in that date range. What are you going to do then? If one customer is listed multiple times in the date range, do you want their name/date to appear each time, or do you want their name to appear just once?


Do you just want to see this in a query/datasheet format, or are you running some sort of Access application and want to see the results in a form?



I'll give a mini-answer: you can do what you want in a query via &quot;parameter queries&quot;--check help for examples (and I think the examples are all based on start dates and end dates, ironically enough).

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[ortega3eduardo]

Well, here is the full scenario. I need to create a vb application that will go an Access database and extract data from a table. This table captures payments made by the customer. I need to capture how many payments were made by a particular customer during a period of time. I created a SQL query in VB to go to the table and extract the data. But now I want it to capture only data during the dates that I specify. This is what I have thus far:

**********************************************************
Public Sub testcmd_Click()
Set objExcel = New Excel.Application
objExcel.Visible = True
objExcel.SheetsInNewWorkbook = 1
objExcel.Workbooks.Add


If mydb Is Nothing Then

Set mydb = Workspaces(0).OpenDatabase(&quot;H:\data\testing.mdb&quot

End If

If myrs Is Nothing Then
Set myrs = mydb.OpenRecordset(&quot;dbotest3&quot
End If


With objExcel.ActiveSheet

myrs.MoveFirst
If myrs.EOF Or myrs.BOF Then GoTo endit

' Run an SQL query to find the total number of accounts taken during the current month by a particular account number ( this can be changed later)

SQLStr = &quot;SELECT Count([user_stamp]) as Fleming from dbotest3 WHERE (((dbotest3.user_stamp)='WEBNAME'))&quot;



Set rsTotal = mydb.OpenRecordset(SQLStr)

**********************************************************
This works but as I mentioned I need it only a particular time period. As you notice I am trying to extract this information to an Excel spreadsheet as well. I got that part covered. Sorry for the long note. I just wanted to clarify what I am trying to do. Also, this needs to be done in VB (Front End ) Access 97 (Backend). Thanks a million.

 

[psemianonymous]

The code helps a million. I think what you need to do is here:

' Run an SQL query to find the total number of accounts taken during the current month by a particular account number ( this can be changed later)

SQLStr = "SELECT Count([user_stamp]) as Fleming " & _
    "from dbotest3 WHERE (((dbotest3.user_stamp)='WEBNAME'))" & _
 " AND [YOURDATEFIELDNAME] BETWEEN " & _
 [yourExcelFormObject]![startDateControl] & " AND " & _
 [yourExcelFormObject]![endDateControl]

That will pull your info. As for filling it into the Excel spreadsheet item by item...good luck.

Note that you have to fill in your form name and your control names in the appropriate places.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[ortega3eduardo]

Thanks a lot for your help. I am very close. I can feel it but I am still not getting the correct output. I entered these parameters:

SQLStr = &quot;SELECT Count([user_stamp]) as Fleming &quot; & _
&quot;from dbotest3 WHERE (((dbotest3.user_stamp)='WEBNAME'))&quot; & _
&quot; AND [date_stamp] BETWEEN &quot; & _
[Form1]![Starttxt] & &quot; AND &quot; & _
[Form1]![Endtxt]

And when I step through it, it appears it is reading the controls correctly but it doesn't capture anything. Am I missing something. Column name date_stamp does have data for the dates I am entering which are 06/01/2003 and 06/30/2003 with the correct user_stamp (WEBNAME). It opens Excel and imports 0. ???
Thanks again. I will continue to chop away at it.

 

[psemianonymous]

Ahh, I think I see it. WEBNAME is something on your form, right?

Unless you have entries in your table that say WEBNAME, you are going to have to change that to Form1!WEBNAME, i.e.

SQLStr = "SELECT Count([user_stamp]) as Fleming " & _
    "from dbotest3 WHERE (((dbotest3.user_stamp)='" & Form1!WEBNAME & "'))" & _
 " AND [date_stamp] BETWEEN " & _
 [Form1]![Starttxt] & " AND " & _
 [Form1]![Endtxt]

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[ortega3eduardo]

Actually 'WEBNAME' is an entry in the table named dbotest3. That's why I have :
FROM
dbotest3
WHERE
((dbotest.user_stamp) = 'WEBNAME'))

user_stamp is a column name in the dbotest3 table. WEBNAME is actually a name that the customer enters on another form not related to mine. Does this make sense?