Searching on date 1

[boab]

Guys I am fully aware of the use of the date functions for VB variables but what I want to do is search a table based on the month and year contained in a date stored in the table do the functions in VBA have SQL Counterparts? Also how would I use them in SQL stement / VBA module?

 

[Norris68]

Both VBA & T-SQL have Month() & Year() functions. You can use them within a SQL query e.g.

... WHERE Month(MyDate) = Month(GetDate()) AND Year(MyDate) = Year(GetDate())

(That used the SQL Server GetDate() function to get the current date/time - you would use the Date() function in VBA/Access)

 

[boab]

so how would I include them as part of a string ?

 

[Norris68]

Eh?
MyString = Month(Date()) & "/" & Year(Date())

You would probably want to use it within your query. It depends what you want to do.

For instance to search for all records in the first quarter of this year based on the field 'DateField' ...
SELECT ... WHERE Year(DateField) = Year(Date) AND Month(DateField) IN (1,2,3)

In Access you can use any public function within a query exactly the same as you would use it in VBA - you can even write your own.

 

[007700]

Hi Guys (girls),

I am pretty new to this, but I have a problem wich I can't seem tot tackle. I need some advanced help.
What I would like to do is create a search that will go trough one table called tblIP_Sheet. This table is filled with loads of IP adresses.
I am looking for a module that will go trough the entire table and list me all the hits found.

I allready tried it with a query, but wasn't able to get that working, tried it with the following module but also wasn't able to get it working the way I would like to.

Option Compare Database

Sub FullLIKESearch()
Dim rs As Recordset, tdf As TableDef, fld As Field, strSearch As String, iResp As Integer
'Search EVERY TABLE, EVERY FIELD in a database for a text string (substring, really)
strSearch = "0" 'this will be parameterized in the sub definition
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) = "MSys" Then GoTo lblNextTable
On Error GoTo errFullSearch1
Set rs = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
On Error GoTo 0
rs.MoveLast
If rs.RecordCount * rs.Fields.Count > CLng(300000) Then
iResp = MsgBox("Okay to do " & rs.Fields.Count & " fields for " _
& rs.RecordCount & " records in " & tdf.Name & " ?", vbYesNo)
If iResp <> vbYes Then Debug.Print "skipped " & tdf.Name: GoTo lblNextTable
End If
'Debug.Print tdf.Name
For Each fld In rs.Fields
'Debug.Print "field " & fld.Name
rs.FindFirst "[" & fld.Name & "] LIKE " & "'*" & strSearch & "*'"
'rs.FindFirst "[" & fld.Name & "] = " & "'*" & strSearch & "*'"
If Not rs.NoMatch Then Debug.Print tdf.Name, fld.Name, strSearch: GoTo lblNextTable
Next fld
lblNextTable:
Next tdf
Debug.Print "DONE"
rs.Close: Set rs = Nothing
Exit Sub
errFullSearch1:
Debug.Print "problem opening " & tdf.Name
Resume lblNextTable
End Sub




Some tips/tricks/help or examples are greatly appreciated....