not really VB more of a SQL question

I have a database with the date field in it and I was wondering how i could use the select statement to get dates from a certain range(to be simply from one month). I tried using SELECT...WHERE thedates >= 1-1-1980 and thedates < 2-1-1980

but it didnt seem to work. Any suggestions?

thanks

[ttingen]

You have to put the dates between # signs in your SQL statement.

Or you could always use a parameterized query in access. Here is an example that I have in an app I developed:

SQL Query:

PARAMETERS [Beginning date] DateTime, [Ending date] DateTime;
SELECT Status, Ticket_Number
FROM Ticket_Record
WHERE Ticket_Record.Date >= [Beginning date]
AND Ticket_Record.Date <= [Ending date] AND Ticket_Record.Status = "Referred";

VB code to pass in the parameters and run the query (I was using DAO in the example):

Function GetWeekly(sQueryName As String, sDateBegin As String, sDateEnd As String) As Integer

    Dim qdf As QueryDef
    
    Set qdf = db.QueryDefs(sQueryName)
    
    qdf("Beginning date") = sDateBegin
    qdf("Ending date") = sDateEnd
    
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    
        If rs.RecordCount = 0 Then
            GetWeekly = 0
        Else
            rs.MoveLast
            GetWeekly = CInt(rs.RecordCount)
        End If
        
    rs.Close
    
    Set rs = Nothing
    Set qdf = Nothing
    
End Function

Hope this helps.

[delker]

Make sure you're using the correct date format, probably like 1/1/1980 and 2/1/1980 (slash instead of hyphen). And I think dates are enclosed in #'s, like this:

SELECT...WHERE thedates >= #1/1/1980# and thedates < #2/1/1980#

You could also use "convert" to make sure the correct format is used.
The "between" operator is another option instead of using the >= and <= operators.

[JHausmann]

You haven't indicated which DBMS your database is using, the suggestions by the other posters work for Access but won't for SQL Server (it wants your dates wrapped in quotes).