Select between dates in Access SQL

[Deleted member 3]

For the past 2 hours I've been getting annoyed with a simple sounding problem, however I seem stupid enough not to be able to solve it.

I have a table with some employees, all kind of irrelevant data, I just need the name and birthday. I want only employees who's birthday are within 2 weeks.

So my first thought would be select firstname, lastname, dateofbirth from employees where birthday is between date() and date()+day(14);

(the actual table uses different names, but since I don't want to confuse anyone with Dutch gibberish and other pointless issues)

Obviously the first idea doesn't work because date() returns 2007 and someones date of birth isn't 2007, it's some other random year. So I decided to create my own date using Day(dateofbirth) & "-" & Month(dateofbirth) & "-" & Year(Date()) (tried swapping mm/dd/yyyy around, using dashes, slashes, dots, nothing etc)

When I use this in combination with a single where clause it works fine, it returns the person whoms birthday is on the given date. However once I use between date() and date()+day(14) it gives me over half the employees and there seems to be no structure in the results. Just random dates. Even when I manually say between 06-02-2007 and 06-03-2007 it does not work. Not does < 06-02-2007, nor when using "06-02-2007" or #06-02-2007# etc)

So the second idea was to use convert(), make sure the new date is actually in date format, however ms access doesn't seem to know convert().

I've been messing around with this query and searching Google for a long time now, seeing how long it takes me I'm sure it's something extremely simple. Otherwise I would have found it/figured it out. But what?

 

[Deleted member 3]

Add to that that isdate() returns -1 for all but 2 records which seem to have empty birth fields, those 2 people were never born I bet. So there is no reason for it not to work, it works in another similar query as well. (which doesn't change the year first)

 

[Alec§taar]

Instead of using the BETWEEN keyword, have you tried simple mathematical evaluations instead?

If you are designing graphically, have you put that last part (w/ the greater than & less than symbols into it) in the CRITERIA filterbox?

(DATE1 > BDAY < DATE2)

* Not sure if this would work for you, but DO try it... the between keyword might NOT be the best way to do this... try math evaluation instead.

So the second idea was to use convert(), make sure the new date is actually in date format, however ms access doesn't seem to know convert().

IIRC, isn't there an IsDate function?

* As far as "structure" in your results? The ORDER BY keyword in SQL can help you...

APK

P.S.=> IIRC also, if you DO try to perform conversions? There are DATE oriented functions in VBA (Visual Basic for Applications) which Access & much of Office uses... DateToStr/StrToDate (iirc, you can check your functions list in Access for these IF they exist) & try it that way... apk

 

[Deleted member 3]

Instead of using the BETWEEN keyword, have you tried simple mathematical evaluations instead?

If you are designing graphically, have you put that last part (w/ the greater than & less than symbols into it) in the CRITERIA filterbox?

(DATE1 > BDAY < DATE2)

* Not sure if this would work for you, but DO try it... the between keyword might NOT be the best way to do this... try math evaluation instead.



IIRC, isn't there an IsDate function?

* As far as "structure" in your results? The ORDER BY keyword in SQL can help you...

APK

P.S.=> IIRC also, if you DO try to perform conversions? There are DATE oriented functions in VBA (Visual Basic for Applications) which Access & much of Office uses... DateToStr/StrToDate (iirc, you can check your functions list in Access for these IF they exist) & try it that way... apk

I tried < and >, they give odd results as well, also sort by sorts the list totally wrong, however isdate() does say they're dates. I work both graphically and plain SQL depening on what I'm making, obviously I've been checking the SQL code here over and over. I haven't tried the strtodate function however (didn't know it existed) I'll try it tomorrow when I'm back at work.
If everything fails I'll just rewrite the whole fucking code and use a recordset and make some spaghetti to get it to work

In 2 weeks I'll be jobless again, whoever is taking over the care of this database will end up in my hell, I made quite some odd things

 

[Alec§taar]

I tried < and >, they give odd results as well, also sort by sorts the list totally wrong, however isdate() does say they're dates.

ORDER BY... have you added that to the end of your query? Not "sort by", but order by... try it.

Also, IF you form a temp table of your results? You CAN "query a query" or that temp table in an attempt to reoder your results... sometimes, these methods ARE the "only way".

I work both graphically and plain SQL depening on what I'm making, obviously I've been checking the SQL code here over and over.

Yup, it is a mixture... the graphical design environs only goes "so far", even w/ the criteria box @ the end.

I haven't tried the strtodate function however (didn't know it existed) I'll try it tomorrow when I'm back at work.

This is where I tend to gain a lot using Access nowadays... I learned it in Access 2.0 (16-bit Windows 3.x days) & there wasn't 1/2 the functions you have available today (like between, afaik it didn't exist back then)... so, I tend to use a LOT of "fundamentals"/basics type commands in my SQL work.

If everything fails I'll just rewrite the whole fucking code and use a recordset and make some spaghetti to get it to work

LOL, I know THAT feeling... especially maintenancing others' work. BUT, occasionally? You DO learn stuff from others' work... you start out saying "WTF? Who was this nut who wrote this, & WHAT was he out to try to do?"...

Sometimes though, you end up learning a new trick though...

In 2 weeks I'll be jobless again, whoever is taking over the care of this database will end up in my hell, I made quite some odd things

Hey, again, I do KNOW that feeling... but, hopefully, you will learn by it. Maintanencing others' work, even though it is "looked upon as software janitor work", is often a great way to learn new tricks/tips/techniques (and, yes, functions in SQL too).



* Good luck, & I hope the IsDate & DateToStr/StrToDate aid you here...

APK

 

[Deleted member 3]

I meant order by obviously. Sort by doesn't exist

It seem strtodate() does not exist, or at least I can't use it in a query.

I did just think of a new way to convert the dates to 2007 to get the birthday using Replace([Geboortedatum];Year([Geboortedatum]);'2007') (geboortedatum = date of birth) however I get a datatype mismatch. So now if only the thing would accept the field as date, can't use cast either. Stupid program.

Funny thing is, this is jsut a small extra someone once suggested, I don't have to make it however I can't accept that it isn't working

I could of course create a table birthday and every time I open the form I could execute a query that replaces the years and then imports it into the table, sounds like that could work and assures whoever finds out i did that will be like "wtf?"

 

[Deleted member 3]

Ok I got it to work, for the user it works just perfectly, on the background it's just silly

I made the table "birthday" and every time the subform that lists them is opened it deletes the data in the table and copies the birthdays to the table. The record source is a query on that table selecting between date() and date()+day(14). Also, this is based on the logged on user. When the personnel people are logged on it shows contracts expiring, when administration logs on (the ones requesting the birthday stuff some time ago) the labels change and the record source changes to the query and the same subform is used for birthdays.
Whoever will develop this system further will so hate me for making stupid code And the company loves me for making things work. Such differences in those worlds.

 

[Alec§taar]

I meant order by obviously. Sort by doesn't exist

Got ya, & I figured that, but didn't want to state it...

It seem strtodate() does not exist, or at least I can't use it in a query.

It probably doesn't exist in VBA quite possibly... I confuse a LOT of Delphi string functions w/ VB ones. IIRC, full VB has it, but VBA (subset of full VB only) may not.

Tip: IF StrToDate is NOT in full VB even? You can find "analogs" written by 3rd party guys @ various VB sites, most likely, & include a MODULE in your code to use it... KEEP THIS IN MIND!

I did just think of a new way to convert the dates to 2007 to get the birthday using Replace([Geboortedatum];Year([Geboortedatum]);'2007') (geboortedatum = date of birth) however I get a datatype mismatch.

Replace ROCKS... I discovered it in VB6 back in 1999 working on an "Enterprise Class" VB-Citrix-Oracle multi-user project, & loved it... far better than Right/Left string work & moving thru the character array to burn or replace stuff!

Datatype mismatches mean you have to perform conversions still as I am sure you know, & some (as I call it) "String Stripping" (replace might work here for burning out "/" in diff. date formats, & I suspect this MIGHT be part of what you're hitting possibly) - or the date format you're working w/ or sending to the functions used is not right, yet.

I hate diff. date formats personally.

OR shift from working strings to numerical data or date data OR format, quite possibly... use diff. functions, OR test your date formats?

So now if only the thing would accept the field as date, can't use cast either. Stupid program.

Some more conversions work sounds in order on your data, OR again, using functions for said diff. data type... it would be my guess, but I don't have your DB here to look @ either, so its just guesswork on my end @ this point.

Funny thing is, this is jsut a small extra someone once suggested, I don't have to make it however I can't accept that it isn't working

Oh, pound away @ it long enough? You'll get it right... look @ it a year from now, & probably say "Gee, I didn't know about this function on that particular datatype" & do it even better...

Looking over your VBA functions list? Paramount... it's great stuff, & the tools you work with.

I could of course create a table birthday and every time I open the form I could execute a query that replaces the years and then imports it into the table, sounds like that could work and assures whoever finds out i did that will be like "wtf?"

Right... I have been that "WTF?" guy, but later found out it was "good kung-fu" & worked... where nothing else would!

APK

 

[Alec§taar]

Ok I got it to work, for the user it works just perfectly, on the background it's just silly

Isn't it a GREAT feeling? Better than chess fighting against your OWN self/mind, finding your own design or function usage errors... up there w/ taking a GOOD leak lol, almost as satisfying as sex!

(Well, not quite, but you get the point I imagine - bad nerdy humor!)

Whoever will develop this system further will so hate me for making stupid code

AT first? They will... once they look @ it & later say "Yup, it's a way" & sometimes in Access or ANY SQL work?? You HAVE to use "chickenwire, rubberbands, & SuperGlue" methods to make stuff work... it's NOT that clean sometimes.

You mentioned REPLACE... & this is a HUGE improvement in VB/VBA stringhandling that didn't exist before & doing it manually? Opens up potential bugs, or is just plain difficult using Right/Left type stringwork functions, for example.

I made the table "birthday" and every time the subform that lists them is opened it deletes the data in the table and copies the birthdays to the table. The record source is a query on that table selecting between date() and date()+day(14). Also, this is based on the logged on user. When the personnel people are logged on it shows contracts expiring, when administration logs on (the ones requesting the birthday stuff some time ago) the labels change and the record source changes to the query and the same subform is used for birthdays.

Yes, like I mention above? SOMETIMES, you have to use extra tables OR temp tables, to do a job right... OR, query over a query (you CAN do this)... whatever works, works.

And the company loves me for making things work. Such differences in those worlds.

Yes... now get that RAISE man! IF not now, then later, once you do a few more "critical fixes" like this one.



* A pleasure helping you out, in what little I have @ this point... but, I love coding hassles, except my own, lol!

APK

 

[Deleted member 3]

Replace is very handy yes, been using it here and there. Though it doesn't replace left, right or mid. For example if you have a string which starts with 1. bla bla and 2. bla bla you can remove the 1. or 2. with left/mid easily. Anyway, I made me questionable code that works, so I'm happy. No more frustration that I couldn't get it to work.

 

[Alec§taar]

Replace is very handy yes, been using it here and there.

It beats the HELL out of doing it manually using other string functions...

I think of it like a Date-Time picker ActiveX/OleServer control, OR even a "user control"...

Yes, you CAN make them yourself... & you can attempt to reinvent that wheel, but many times? You overlook things & exceptions IN THE DATA ITSELF, & better have an "On Error GoTo" (VB6/VBA iirc on the latter for err handling) in place to stop crashes.

Though it doesn't replace left, right or mid. For example if you have a string which starts with 1. bla bla and 2. bla bla you can remove the 1. or 2. with left/mid easily.

No "REPLACE doesn't replace" (pun intended) those functions, but you can bet it UTILIZES them... I had to use those before there WAS a 'replace' function, to emulate it, & it was the source of many an error, & TONS of errtrap exception code, especially w / 'bad data'...

Anyway, I made me questionable code that works, so I'm happy. No more frustration that I couldn't get it to work.

I bet you are happy w/ yourself today... lol, see? YOU DID IT!



* Congrats! Glad to help again, where I could...

APK

P.S.=> If you work w/ other coders on your team/company? I suggest getting into the habit of using that "second impartial eye" (other coders)... they spot things you MAY not! It helps... we ALL do it too! apk

 

[Deleted member 3]

P.S.=> If you work w/ other coders on your team/company? I suggest getting into the habit of using that "second impartial eye" (other coders)... they spot things you MAY not! It helps... we ALL do it too! apk

I work alone, no IT related figures here at all. Every small issue ends up on my doorstep.

What you're suggesting doesn't only work with coding, works with anything. When I create things I let others read through them and they find errors. You always miss your own errors, be it coding, spelling, maths or anything.

 

[Alec§taar]

I work alone, no IT related figures here at all. Every small issue ends up on my doorstep.

You can probably snag a raise then, @ SOME point, because you are that "go to guy"... think about it.

Don't push TOO hard, but the point is there... you are ALL they've got apparently! This? WORKS IN YOUR FAVOR, hugely, for commanding more monies.

What you're suggesting doesn't only work with coding, works with anything. When I create things I let others read through them and they find errors. You always miss your own errors, be it coding, spelling, maths or anything.

So true...



* I guess, feel free to ask me stuff on this again, & I will try to help (it's tough remotely, especially w/ out seeing the data in tables etc. OR your code, lol, but I've seen some of the stuff you have & mention above, & things you haven't (like IsDate for example from above))...

I would have come to the point where I would have asked you to send me the actual .mdb file to look at, but this can be a security issue... & secondly? I don't keep Office 2003 online anymore on THIS box... too many security holes in Office lately, & until they are patched up fully & proven?

I omit installing it, for now @ least.

APK

P.S.=> Again: DO look thru you functions lists, in Access VBA: As this is your toolset, I can't stress that enough! It's FAR better than building 'rigjob' fixes many times in 'homegrown code' you rig up yourself, especially w/ exceptions data (problem data)... apk

 

[Deleted member 3]

Contract ends in 9 workdays. So no raise for me

Not sure where I'll be next month, though until then I'll be full time TPU employee. I should ask W1z for a raise, he will probably only agree to a raise in percentages though. A big one even. 0+200% = ehm... damn W1z

 

[Alec§taar]

Contract ends in 9 workdays. So no raise for me

That's ok... next job/contract? Ask for more... not a lot, but more. Keep doing it each time, before you know it? You're making really decent ca$h!

THERE OUGHT TO BE A PROGRAMMER'S LABOR UNION imo... we get TOO screwed by 'headhunters' & such (this is experience talking - they make monies for doing SQUAT while we work: THIS IS WRONG!)

Not sure where I'll be next month, though until then I'll be full time TPU employee. I should ask W1z for a raise, he will probably only agree to a raise in percentages though. A big one even. 0+200% = ehm... damn W1z

Perhaps a "MODERATORS/ADMINISTRATORS" union is in order @ TPU? LOL, as I wait for a ban on myself for such a suggestion!



* Sarcasm W1zz...

APK

 

[Deleted member 3]

I'm officially not a coder/programmer/haxxor. My diploma says I'm network/application admin. Then again I never really specialized in anything, I'm still young though

 

[Alec§taar]

I'm officially not a coder/programmer/haxxor. My diploma says I'm network/application admin.

GOOD! Then, you're doing what I did pretty much: Work BOTH ends of it, networking & coding... you're prepping yourself, for both.

Best coders I know? Do both... you need both, to understand what hassles may exist. "Pure coders" (or pure networkers) can't do this I have seen/found.

You're becoming what I call "The computerman of yesteryear" where you HAD to know or do it all (or, cobjob/rig it, lol)... who is the computerman of tomorrow!

(If only for competitive purposes only, if anything... jacks of all trades/masters of some, are the way of the future in this field imo, especially today w/ the net available for reference as you & I have just done to help solve a hassle of yours).

Then again I never really specialized in anything, I'm still young though

Sky's the limit for you then... I think that you'll find, eventually, that you'll start getting what I call "TOO MUCH EXPERIENCE" & will have trouble finding coding jobs for the wage you will eventually ask for, due to experience & successful trackrecord/good references, from your resume...

That is the time I am hitting... & that, to me? Means it's time to hit my initial intended goal: Mgt. in this field (higher monies, less work imo, but MORE responsibility - which is not bad on the latter, IF you know how to identify the RIGHT man for the job & experience GIVES you that)

Experience also helps as well in that YOU have the ability to help if a deadline's looming & still buggy app code in place - makes you the BEST mgr. there is, imo @ least... best bosses I had ever, were guys that had done the job & could do it as well as I could (OR BETTER in a couple cases!)

If you haven't noted this already? You'll find MANY companies via headhunters, hiring guys that are relatively "Green" still in this area... why??

THEY COST LESS, & the headhunter can make more off of them... & the company pays out less @ times also for younger newer greener talent.

(They can do the job, a bit slower sometimes, but can get it done... & if only say, 5% slower or code is not 'quite as clean' but works? Who cares... money talks!)

APK