Source language: Translate to:

Date Update

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Date Update

Postby Carl_SA » Mon Nov 19, 2007 7:16 am

I have built an application which makes use of 2 Access databases. I run a query and update the results into a table in one of the databases.
In the update of database1 I have a number of variables that I set the values based on values in database2. I use the "setvar" command and this works successfully. The problem that I have is that when I want to update a date field in database1 based on a date value in database2 it does not update.
The command is:
SetVar "[Man_Rep.Master.Date]" "[comair.tblMerlynn.Date]"
dbpAddRecord "Man_Rep" "Master"

All the other variables including text and numbers work fine. Is there some setting required for a date? Please help
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Gaev » Mon Nov 19, 2007 8:03 am

Carl_SA:

a) Start with this code to ensure variables contain values you expect ...
Code: Select all
AlertBox "tblMerlynn" "[comair.tblMerlynn.Date]"
SetVar "[Man_Rep.Master.Date]" "[comair.tblMerlynn.Date]"
AlertBox "Master" "[Man_Rep.Master.Date]"
dbpAddRecord "Man_Rep" "Master"
... and perhaps you can tell us how you defined the fields called "Date" in each of the databases ... as well as the results of the AlertBox commands.


b) As reported in another post, MS Access seems to require input date values to be ...

- wrapped in # signs
- and be in american date format

... i.e. # mm/dd/ccyy # ... so you might try ...
Code: Select all
DateToNum "[comair.tblMerlynn.Date]" "Default" "[carlNum]"
NumToDate "[carlNum]" "m/d/yyyy" "[carlDate]"
SetVar "[Man_Rep.Master.Date]" "# [carlDate] #"
... don't know if this will work ... just a wild guess !
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Carl_SA » Mon Nov 19, 2007 8:35 am

Hi Gaev,

I have tried surrounding the variable with the #. I also did verify that the [comair.tblMerlynn.Date] is valid. I made use of a text field for this. The [comair.tblMerlynn.Date] presents perfectly but somehow the 2nd database just won't accept it.
Both databases have the field as dates in the yyyy/mm/dd format.
I will try and convert datetonum and back and see if this helps.

Thanks for the response on this.
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Gaev » Mon Nov 19, 2007 9:03 am

Carl_SA:
I also did verify that the [comair.tblMerlynn.Date] is valid.
OK
The [comair.tblMerlynn.Date] presents perfectly but somehow the 2nd database just won't accept it.
Remember that this is just another variable in NeoBook's variable-domain ... so checking it is just to make sure that it contains the right source value.
Both databases have the field as dates in the yyyy/mm/dd format.
The Help file states ...
Date
Date. This type is not supported by MS Access, use DateTime instead.**
Time
Time. This type is not supported by MS Access, use DateTime instead.**
DateTime
Date and time data.**
So use at your own risk.
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Mon Nov 19, 2007 9:35 am

I believe you have to add the record BEFORE you set the var?
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Carl_SA » Mon Nov 19, 2007 10:18 am

Hi Wrangler,

I have 15 other variables that update correctly. I follow the sequence of setting the variables before adding the record. All the other fields perform perfectly but not the date.

I did try your version but it did not work
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Wrangler » Mon Nov 19, 2007 10:23 am

OK. Wasn't sure if that was it. There are a lot of particulars with date and time in access. A google will probably bring up the information you need to make it work. I've been googling access stuff a lot recently.

Have you tried datetonum and numtodate? You could store the date as an integer instead of date. Might be easier than beating your head against the wall trying to figure out the anomolies of Access. :D

You could also try dbpsaveedits after you add record and setvar.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Carl_SA » Mon Nov 19, 2007 10:31 am

Hi Wrangler,

Did try the datetonum and then back again. Could not get it to work.

Thanks for the time on this anyway
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Gaev » Mon Nov 19, 2007 10:51 am

Carl_SA:
Did try the datetonum and then back again. Could not get it to work.
Perhaps you misunderstood Wrangler's suggestion ... define your fields named "Date" as numeric ... and instead of inputing something like 02/11/2007, you input the result from doing a DateToNum ... so it is just another numeric field as far as MS Access is concerned ... and each time you want to extract this data ... you do a NumToDate on the numeric data.

If this is not working, you might have something else wrong ... like a typo in the variable name ... BTW, never a good idea to use field names like "Date" ... could clash with "reserved names" ... something like StartDate or EndDate or DateOfBirth or even xDate is preferable.
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Carl_SA » Mon Nov 19, 2007 11:01 am

Hi Gaev,

I did try to rename the Date variable but to no avail. When I do a datetonum conversion then it does record a value. The problem is that my application allow the user to create their own report in the form of an Access table. They then use Excel to view this data.
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Postby Neosoft Support » Mon Nov 19, 2007 12:01 pm

NeoBook may be misinterpreting the date as a mathematical formula. For example:

11/19/2007

might be seen by NeoBook as:

11 div 19 div 2007

To prevent this add an exclamation point character (!) in front of the date. For example:

SetVar "[Man_Rep.Master.Date]" "![comair.tblMerlynn.Date]"

Also, I think you should add the new record before setting any variables, then call sbpSaveEdits to make sure the data is posted to the table. For example:

dbpAddRecord "Man_Rep" "Master"
SetVar "[Man_Rep.Master.Date]" "![comair.tblMerlynn.Date]"
...
dbpSaveEdits "Man_Rep" "Master"

Finally, if you're using dbpSetFieldProperties, make sure you haven't set the date field to read-only. This will prevent you from posting any data to that field.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby Wrangler » Mon Nov 19, 2007 12:10 pm

It seems odd that setting the var before adding the record would even work. It never worked for me.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Mon Nov 19, 2007 2:25 pm

Carl_SA:
The problem is that my application allow the user to create their own report in the form of an Access table. They then use Excel to view this data.
... try and define the "Date" field in the "Master" table of the "Man_Rep" database as a string ... and as per Dave's suggestion ...
Code: Select all
SetVar "[Man_Rep.Master.Date]" "![comair.tblMerlynn.Date]"
If that doesn't work, try the old brute force ...
Code: Select all
SetVar "[Man_Rep.Master.Date]" "!2007/11/19"
If it still does not, you might setup a .zip with the .mdb files and a sample test pub ... one button with code to read the dates ... another to write to the other database ... and Text Boxes showing the various values ... and make it available for review by others.

BTW, what is the Windows Regional Setting for Dates on the machine you are testing ? ... and will it be the same on your users' machine ? ... note that databases will expect incoming data (and present requested data) in the format specified for the field ... which may not always be the same as the user's Windows settings.
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Mon Nov 19, 2007 2:31 pm

Here is an article on how to change the date format in the registry. First read the registry for the format that is being used, then write the new format to the registry, and upon closing the program, write the original back. I use this in one of my programs for my Canadian customers, who use a different format than here in the US.

http://support.microsoft.com/kb/241671
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Carl_SA » Mon Nov 19, 2007 8:57 pm

Thanks so much for the suggestions.
It will take me a while to work through. I will give feedback based on what works.
Carl_SA
 
Posts: 91
Joined: Sat Dec 17, 2005 12:47 am

Next

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest