database: extracting a date range?

[wjcampbe]

Try

$sql = "SELECT [Date Ordered], [Order Number] FROM [Order] WHERE [Date Ordered] LIKE #" & '$fromday/$frommonth/$fromyear' & "# ORDER BY [Order Sequence Number] ASC;";

[wjcampbe]

Here is an actual Access SQL statement that worked for me

SELECT Order.[Order Sequence Number], Order.[Order Number], Order.[Total Cost], Order.[Total Lines], Order.[Date Ordered]
FROM [Order]
WHERE left(Order.[Date Ordered], 10) > '2006/11/02' AND left(Order.[Date Ordered],10) < '2006/12/02';

[Jan]

So Bill, I now agree with you completely and Gabe I see your problem and Zoltan - pah is all I can say to you (but it was funny).

Regards,

[gabrielcrowe]

bill, thats heroic.

why arent they stored as datetime? eeshk, zoltan! who created this database schema? they need to be shot!

[gabrielcrowe]

bill:

the range was wrong and causing bad errors, so this

>= + <=

fixed it. thanks for the help.

[Jan]

I'm not Zoltan but I'll answer anyway I was responsible for the database for quite a while and did make some improvements but those dates were part of the original schema, which would have been designed by one of the original Actinic developers.

Regards,

[Darren B]

Hey Jan you passing the buck

[Jan]

You could say that or you could say that I'm not taking the glory ... depends on your viewpoint, I'm quite proud of being the Actinic database person for a while (although I do agree that it could be improved)

There is actually a good reason for not using date strings by the way, timestamps are not portable but storing the dates in that manner is.

Regards,

[leehack]

Did you used to work for actinic then and branched off into your own stuff instead?

[Jan]

I did - joined at the same time as Zoltan. I still work for Actinic some of the time now as well.

Regards,

[brian.mc]

lol, sadly, i only drink fine whiskey.

Will that be Jamieson's then? Sorry to be pedantic, but whiskey is Irish, which is good, but if it's fine malt whisky you want you'll have to come to Scotland!
I'm a Macallan man myself, but will take Glenfiddich or Glenmorangie at a push.

[Darren B]

Brian funny you should say that

the irish spin on whiskey is that that scotch is ermm an attempt but for real whiskey you need to have irish, they dont filter it the same way, apparrently. So i have been told by the FiL

Jan

There maybe 24 hours in a day but i can think of alot better ways to use them than working mind some people would also call that working aswell

[gabrielcrowe]

<rant>

The database has an utterly disasterous design. there are now odd add ons from several versions, lingering in design limbo. There are things in there, that they tought me not to do at #school#. Even the documentation written by the developers is an odd read.

A table called 'order'?

wtf? isnt that a reserved word? and various camel case oddities, as well as a poor choice of normalisation solution.

...its a wierd place to fool around in, and the way that it breaks so often, is a testament to how 'slap features in' it is. very much like windows.

thanks for those of you that helped me get the sql i needed, but frankly, it'd be a hell of a lot easier, if the database was simply in a better format.

</rant>

ok, i'm done. *takes a breath*.

[Jan]

The database is around 10 years old now and has been added to by lots of different people over the years, you are going to find a lot wierder stuff than dates in string format as you start to use it more. A lot of it isn't so obvious either.

Regards,

[gabrielcrowe]

mind, i have other software that's even worse. especially some linux software that has has millions of updates.