SQL problems

  • Thread starter Thread starter lee
  • Start date Start date
L

lee

hi all,

i am trying to pull data from a table with the date of a
field within a specific month.

i want the user to enter the month in MM format, then
alter the SQL string to pull from the required table.
ie,

strSQL = "SELECT * FROM TableName WHERE DateSubmitted >='"
& strmonthstart & "' and datesubmitted <'" & strmonthend
& "'"

strmonthstart is inputted earlier as 01/MM/2003 (does this
have to be american format?)eg, 01/07/2003

strmonthend is calculated from strmonthstart as + 1 month,
eg 01/08/2003

if i haven't made any sense, please let me know. any help
is appreciated greatly. i've no hair left!!!

is the SQL syntax correct? is there an easier way for a
user to select the month to pull records from?
 
Yes, the dates Have to be in US format and normally date strings need to be
surrounded with # marks.

strSQL = "SELECT * FROM TableName WHERE DateSubmitted >=#"
& strmonthstart & "# and datesubmitted <#" & strmonthend & "#"
 
is the SQL syntax correct? is there an easier way for a
user to select the month to pull records from?
Click to expand...

I'd be tempted to use the MONTH function:

WHERE YEAR(DateSubmitted)=2003
AND MONTH(DateSubmitted)=[Enter month as a number:]

but you'll have to remember to update it for next year, or add the year as
another parameter...

An alternative would be

WHERE DATEVALUE(DateSubmitted)
BETWEEN DATESERIAL(2003, [Enter month as a number:], 1)
AND DATESERIAL(2003, [Enter month as a number:]+1, 0)

The datevalue is neccessary to pick up odd behaviour if there are
timevalues attached to the DateSubmitted value.

All the best


Tim F
 
Back
Top