Source language: Translate to:

dbpQuery DATE Format

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

dbpQuery DATE Format

Postby Simanest » Thu Sep 13, 2007 5:41 pm

Can anyone help please.
I am running a dbpQuery linked to MS Access dbase. I am having problems searching dates. I can search my 'Date' field with no problems but i only want to show records between certain dates. This is as follows:

dbpQuery "base" "BASE" "date BETWEEN [#34][DateFrom][#34] AND [#34][DateTo][#34]"

'DateFrom' & 'DateTo' being my user input strings for dates to search between.
When i run the script it works fine apart from the fact that it is not taking into account that i am working in Date format.
I.E. the date search range '010707' TO '010708' according to the system is only one number larger than the other and not a whole year. I know this is probably silly and i am new to this but a little help would be much appreciated. I guess its something to do with number fields & date fields in access.

Many thanks

Sigmanest
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm

Ive Cracked It! Very Happy

Postby Simanest » Fri Sep 14, 2007 3:05 am

I fixed my problem by usig the dbpQuery 'LIKE' Function.
dbpQuery "base" "BASE" "date LIKE [#34]%[MonthYear][#34]"

This returns all the dates between 010707 & 310707

Works really well.

It has fixed my issue. Bareing in mind i didnt need to find a specific date in a month, just a filter of all records in that month.

HAPPY COOKIE :)
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm

Re: Ive Cracked It! Very Happy

Postby dpayer » Fri Sep 14, 2007 6:48 am

Simanest wrote:I fixed my problem by usig the dbpQuery 'LIKE' Function.
dbpQuery "base" "BASE" "date LIKE [#34]%[MonthYear][#34]"

This returns all the dates between 010707 & 310707

Works really well.

It has fixed my issue. Bareing in mind i didnt need to find a specific date in a month, just a filter of all records in that month.

HAPPY COOKIE :)


I'm curious. Is the data in a column formated as "DATE" or was it just text / integer?

David P
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Just Text

Postby Simanest » Fri Sep 14, 2007 2:46 pm

The data in the Access field was in text format. I needed this to be like this for the other part of my application to function properly. At no time did i want a date to be in the date format 01/07/07. I just wanted to use simple text fields with the date as a simple entry of 010707.
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm

Postby Gaev » Fri Sep 14, 2007 5:23 pm

Simanest:
When i run the script it works fine apart from the fact that it is not taking into account that i am working in Date format. I.E. the date search range '010707' TO '010708' according to the system is only one number larger than the other and not a whole year.

The data in the Access field was in text format. I needed this to be like this for the other part of my application to function properly

1) How did you expect MS Access to determine that you had a text field with a date format (ddmmyy) ?

2) I don't know if MS Access's BETWEEN xxx AND yyy qwery supports text fields ... or just pure numeric fields ... but if it does, the least you would have to do is store your dates in yymmdd format ... to be safe, ccyymmdd ... in case you have dates that are in multiple centuries.

That way, 11th of April 2007 (070411) is determined to fall between (say) 6th of March 2007 (070306) and 4th of September 2008 (080904) ... the way you had it, you would compare numbers between 060307 and 040908 ... not only is the first number larger than the second ... but 110407 is larger than both ... and would be considered outside the specified range.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Simanest » Sat Sep 15, 2007 1:00 am

I expect access to know its in text format because i set the properties of the fields in access to be text only.

What i have done is managed to sort my records by using just a number as a date which is what i wanted for my application. The number 220707 will always have the 0707 part of it no matter what as that is the month and year. The same as 160507. the common factor of this number will always be 0507. The day is not important to me as long as its in a particular month. So my script now sorts an access database by the month year and collects all days in that month. This was what i wanted.
What i need to mention here is i also have written a barcode scanning software that puts the dates into access in this way, i.e. number into a text field as this is the limitations of the hardware so this is why i needed Neo to sort by a number rather than an official date format.
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm

Postby Gaev » Sat Sep 15, 2007 5:58 am

Simanest:
I expect access to know its in text format because i set the properties of the fields in access to be text only.
Yes, but how does MS Access know to treat it like a date in the ddmmyy format ?

What i have done is managed to sort my records by using just a number as a date which is what i wanted for my application. The number 220707 will always have the 0707 part of it no matter what as that is the month and year. The same as 160507. the common factor of this number will always be 0507. The day is not important to me as long as its in a particular month. So my script now sorts an access database by the month year and collects all days in that month. This was what i wanted.
As I said in my earlier post ... if you stored your dates in your text field as yymmdd (instead of mmddyy), you can subsequently "sort" and "query ranges" in a single step ... the key point I was making was that when storing dates in Text fields, the order should be century (optional) year, month and then day.

What i need to mention here is i also have written a barcode scanning software that puts the dates into access in this way, i.e. number into a text field as this is the limitations of the hardware so this is why i needed Neo to sort by a number rather than an official date format.
NeoBook has commands for string manipulation ... to convert from mmddyy to ddmmyy ...
Code: Select all
SubStr "[scandate]" "5" "2" "[scanyy]"
SubStr "[scandate]" "3" "2" "[scanmm]"
SubStr "[scandate]" "1" "2" "[scandd]"
SetVar "[databaseField]" "[scanyy][scanmm][scandd]"
Using NeoBook's Function facilities, you can even reduce this down to one line like ...

Call "ddmmyyTOyymmdd" "[scandate]" "[databaseField]"
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Simanest » Sat Sep 15, 2007 6:27 am

Yes i see what you mean by using the date in the yymmdd format. So simple when you suddenly realise! Some things just arnt clear at the time. Many thanks for that!!!
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest