Populating Form Date Field Control with MySQL Query Field?

Creating and using forms
Post Reply
leeand00
Posts: 63
Joined: Fri Feb 22, 2008 12:09 am

Populating Form Date Field Control with MySQL Query Field?

Post by leeand00 »

Note: All dates are of MySQL Type DATE

Code: Select all

IF( ISNULL( `job`.`next_date` ), DATE_FORMAT( ADDDATE( `job`.`last_date`, 36 ), '%m/%d/%Y' ), DATE_FORMAT( `job`.`next_date`, '%m/%d/%Y' ) ) AS `TheNextDate`
Now when I display the results of this field in my query in a date field control in a form in Base,

The control always displays as: 01/01/1800

...dispite that when I run the query outside of the form I get a result of TheNextDate: 12/14/2010 (when the next_date field isn't NULL). :crazy:

Does this have something to do with the way the field is being returned, and the way the field is being returned?

I have some other Date Field controls that are filled in by other fields in the same query and they appear correctly...
for instance something as simple as:

Code: Select all

DATE_FORMAT( `job`.`last_date`, '%m/%d/%Y' ) AS `LastContactDate`
appears just fine.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Populating Form Date Field Control with MySQL Query Fiel

Post by FJCC »

I looked at this problem and found I could use either a Date Field or a Text Box to get the date to display in the desired format, depending on the function used in the query. I set up a form with a Date Field and a Text Box, both linked to a query like

Code: Select all

SELECT DATE_FORMAT( MAX( "TEST_DATE" ), '%m/%d/%Y' ) FROM TABLE1
The Date Field showed 1/1/1800 and the Text Box showed 12/14/2010. I change the query to

Code: Select all

SELECT ( MAX( "TEST_DATE" ) From Table1
and the The Date Field showed 12/14/2010 and the Text Box showed 40524.64.
My guess is that DATE_FORMAT returns a string, not a DATE type or a number that can be interpreted as a date, which causes the Date Field to return its minimum value. The MAX() function returns a number that is correctly displayed as a date by the Date Field.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Populating Form Date Field Control with MySQL Query Fiel

Post by rudolfo »

If in doubt about a type that is based on a mysql function and you don't want to look up the documentation for this function, you can build a view (in Mysql direclty, not in Base) and check the colum types of the view:

Code: Select all

 CREATE VIEW will_be_deleted AS
SELECT .... your select statement is here ...

DESC will_be_deleted;

Example (the_table has an integer column "time" with the number of seconds)
   I want to see 04:45 and not 00:04:45 if the time is less then one hour
CREATE VIEW the_view AS
SELECT if((time < 3600),
          time_format(sec_to_time(time),'%i:%s'),
          time_format(sec_to_time(time),'%H:%i:%s')) AS duration FROM the_table; 

DESC the_view;

+----------+-----------------------+------+-----+---------+-------+
| Field    | Type                  | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+-------+
| duration | varchar(13)           | YES  |     | NULL    |       |    VARCHAR(13) a string type!
+----------+-----------------------+------+-----+---------+-------+

Okay, a sec_to_time nested in a if is a bit to complex for the SQL parser in Base. At the end of the day I had to use the view permanently in the Mysql database. In Base it simply appeared as another table on which you can do any read-only operations.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Post Reply