SQL agian

[ZanM]

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

[lenin]

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

[ZanM]

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

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]

[Gary.Lowe]

ZanM

Don't know if this will help

'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

[ZanM]

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.