SQL agian

I'm trying to retrive all the info in a table that falls in the current month

this is what I tried

SELECT ClientData.Address, ClientData.Name, ClientData.LastWork, ClientData.NextScheduled, ClientData.CustomerType, ClientData.JobStatus
FROM ClientData
WHERE (((ClientData.NextScheduled) Like (ClientData.NextScheduled)=Date()))
ORDER BY ClientData.Address;

it doesn't work
I don't think I'm using =Date() the right way

Hi,
if you are using access you can set the month to a number and use the datepart function:

example:

SELECT *
FROM repl_test1 where
datepart("m", [date_start]) = 3

This selects everything for marcch of any year. This can be used in combination with "yyyy" - year etc to narrow your search criteria.

HTH.

Lenin

I'm doing it in vb and I've tried using access it doesn't help any even when I use it to generate the SQL

this is what I'm trying now

SELECT * FROM database WHERE database.datefield LIKE
"DATEPART('MONTH','GETDATE()");

this also does not work sombody please help me out here

project http://evodev.com/ClientDbase.zip
Database http://evodev.com/ClientBase.zip
an ocx the project needs http://evodev.com/floatbutton.zip

I really need the help getting this report to generate it is the important part the project the whole reson I was commetioned to do it

thankx to anybody who even downloads and looks at the stuff

Code:

---

Dim dTemp As Date
dTemp = Month(Now)

oRS.Open "SELECT Address, Name, LastWork, NextScheduled, CustomerType, JobStatus
FROM ClientData
WHERE NextScheduled=#" & dTemp & "# ORDER BY Address;", oConn

---

I'm not much of a guru when it comes to databases, but I think that's about right. oConn is the connection object you're using, oRS is the recordset. I'm not sure about the hashes around the date, but I'm pretty sure I read somewhere that you had to do that (like quotes around a string - it's sequel thing! :) )

(Tell me if it works and I'll be well chuffed! :) )

[Edited by matthewralston on 05-31-2000 at 06:44 AM]

ZanM

Don't know if this will help

Code:

---


'Find out the current Month Number and Yrea Number
'set iMonth and iYear to those values
'to equal that number
Dim iMonth as Integer
Dim iYear as Integer

SELECT * FROM ClientData
WHERE (((DatePart("m",[dateField])=" & iMonth & ") And (DatePart("yyyy",[OriginalDate])=" & iYear & ")));
ORDER BY ClientData.Address;

---

Hope this works

Gazza

The problem was that I was comparing part of the Date(month) to all of my date field. I just had to get the month from the field and now it works. And of corse then I did the same for the year so it will be from only this may not all the mays from 80 - 2099.

Thankx everybody.