Sql syntax help (1 Viewer)

craigachan · Apr 18, 2015

I have a table called [calendar] with text field [month], [day], and [year]. Other fields are 'PID', (the patientID) All of these fields are short text. This table is used for an patient appointment schedule. and each record is a patient appointment that is used to populate a schdule calander. With the following sql I get all dates and they are all ordered correctly. When an appointment get's cancelled, instead of deleting the appointment, the date gets changed to 99/99/9999 for [month]/[day]/[year].

Asql = "SELECT * FROM [calendar] " & _
"WHERE id = '" & strPID & "' AND [year] <> '9999' AND " & _
"[month] <> '99' AND [day] <> '99' AND " & _
"ORDER BY [year] DESC, [month] DESC, [day] DESC"

This sql works correctly. And then I want to filter out all dates less then Date() with the following sql

Asql = "SELECT * FROM [calendar] " & _
"WHERE id = '" & strPID & "' AND [year] <> '9999' AND " & _
"[month] <> '99' AND [day] <> '99' AND " & _
"[month]/[day]/[year] >= " & Date & " " & _
"ORDER BY [year] DESC, [month] DESC, [day] DESC"

But when I add the 4th line in the above Asql statement, it doesn't exlude dates earlier than Date(). I've tried to format([month]/[day]/[year], 'mm/dd/yyyy') but his did not help.

I also tried "#[month]/[day]/[year]# >= " & Date & " " then I get a syntax error with runtime 5 - Invalid procedure or argument.

Can anyone comment on what I'm doing wrong. Unfortunately the [month], [day], and [year] fields are strings and cant be changed. Thanks for any help.

spikepl · Apr 18, 2015

When you divide month by day, and then by year, then you get a very small number!

Your db is a mess, and you should not operate on strings when dealing with dates. Further, gogle reserved words access - you shoiuld not use Access functionname like eg "month" for field name.

Also, google access date functions there you'll fx find functions to create dates from numbers for a given year, month and day.

Now if your data come to you as it does, then make a column containing a proper date, and construct that date from the numbers held in your year, month and day strings. Pay attention to data types.

A date as 99/99/9999 is a silly idea because this is outside the range of dates in Access. Set the field to Null to signify no data.

craigachan · Apr 18, 2015

Thanks for your reply spikepl.

My table is actually an odbc shared sql table from a practice management software which I'm going to have to deal with. I pull my hair out everyday dealing with their strings. But moving on. can you help me put the string together that can convert the [month], [day], [year] fields to numbers and then a proper date? I've tried:

"SELECT *, [month] & "/" & [day] & "/" & [year] AS myDate FROM [calender]...

and then tried to reference myDate in sql string, but I still cant get it to run. I've actually tried to use hard numbers in my sql like:

Format(#4/18/2015#, 'mm/dd/yyyy') = " & Date

But this also fails with an runtime 5 Invalid Procedure Call or agrument.

When I msgbox my sql, I'm able to get the right numbers to show up as in the format statement above. But there must be something wrong with using the string as is in my sql statement. Any ideas?

Fran Lombard · Apr 19, 2015

Can you use temp tables?
I ran a quick test against a mssql express 12 db and this worked

The first table [TestDates] represents yours existing table

Insert some test data - including a record with your 9999s

The next table [#mytab] is a temp work table with a new smalldatetime column

Then copy the data into the # table - excluding the 99s and casting the string date fields to one new field

Then perform your final select including the date filter

Code:

create table TestDates (
     id int
   , mo varchar(2)
   , dd varchar(2)
   , yy varchar(4)
	  )

	go
	  insert TestDates select 1,1,1,2000
	  insert TestDates select 2,2,1,2000
	  insert TestDates select 3,3,1,2000
	  insert TestDates select 4,99,99,9999
	go

--	select * from TestDates
go

create table #mytab ( 
    id int
  , mo varchar(2)
  , dd varchar(2)
  , yy varchar(4)
  , myDate smalldatetime
  )
	  go

insert into #mytab select *,  cast(mo + '/' + dd + '/' + yy as smalldatetime)
  from TestDates
 where mo <> '99'

select * from #mytab
where myDate < '03/01/2000'
go
drop table TestDates
drop table #mytab


----------------  RESULT   --------------------
id          mo   dd   yy   myDate
----------- ---- ---- ---- -----------------------
1           1    1    2000 2000-01-01 00:00:00
2           2    1    2000 2000-02-01 00:00:00

It would probably be best to create a stored procedure that would take your date filter as an input parameter and return you a recordset of the fields you want

Hope this helps

craigachan · Apr 19, 2015

Thank you Fran,

Thats exactly how I worked around my problem. I created a temp table from the original [calander] table and assembled the dates in a proper date format into that temp table. Then excluded the unwanted records when creating a rowsource for my listbox.

Thanks again everyone for your help

Sql syntax help (1 Viewer)

craigachan

Registered User.

spikepl

Eledittingent Beliped

craigachan

Registered User.

Fran Lombard

Registered User.

craigachan

Registered User.

Similar threads

Users who are viewing this thread