473,320 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Change of date when using an UPDATE query in CF

139 100+
Hi!

I have data returned from a database displayed on a webpage for amending.

A date on the form is selected using a pop-up calendar which returns it to Access. When the date is retrieved the dateFormat function is used to display it as dd/mm/yyyy.

However, when I run an update query, allowing the user to change any field and refind the record, the date is always changed to 30/12/1899.

Is there a problem with my update query?

update tblWorkshops
set WORecdate = #form.WORecdate#,
where ID = #ID#

I have not used createODBCdate(variable_Name) as I don't know where it needs to be created (ie on the cfm webpage or the action script that updates the database).

Thanks!
Neil
Oct 3 '08 #1

✓ answered by ndeeley

Acoder,

Its okay - I have fixed it. My mistake(Doh!) - I was passing the update value of the date as a number.

The smallest thing, eh?
Thanks for your help.
Neil xxxxxxxxxxxxxx

8 4274
acoder
16,027 Expert Mod 8TB
Use cfqueryparam for the data input and set the CFSQLType attribute to use the CF_SQL_DATE type.
Oct 3 '08 #2
ndeeley
139 100+
Use cfqueryparam for the data input and set the CFSQLType attribute to use the CF_SQL_DATE type.

Hi acoder,

Do I do this on the .cfm form page or the action script behind the form?

Thanks
Neil
Oct 6 '08 #3
acoder
16,027 Expert Mod 8TB
It has to be within the query, so that would be the action page.
Oct 6 '08 #4
ndeeley
139 100+
It has to be within the query, so that would be the action page.

Acoder,

Thanks for your help, I`m slowly piecing it together...

Neil (i`m a designer dammit, not a coder!)
Oct 6 '08 #5
acoder
16,027 Expert Mod 8TB
No problem. If you get stuck, post your code and I'll see what I can do.
Oct 6 '08 #6
ndeeley
139 100+
No problem. If you get stuck, post your code and I'll see what I can do.

Yup, I`m stuck!

The query takes an inputted workorder number, matches it against the field in the database and returns the unique Workorder value and associated data. It's when the record is updated that the date value is being changed. Here's the associated code:

Find record code:
Expand|Select|Wrap|Line Numbers
  1.  
  2.     <cfquery datasource="repairmdb" name="GetClientNo">
  3.     select         ID, 
  4.             WorkshopFK, 
  5.             ClientFK,
  6.             SiteFK,
  7.             WORecdate,
  8.             ResponseLevelFK,
  9.             ClientWONO,
  10.             WorkshopWONO,
  11.             WorkshopJOBNO,
  12.             AssetNO,
  13.             AssetClassFK,
  14.             LocalID,
  15.             ManufacturerFK,
  16.             SerialNO,
  17.             BuildYear,
  18.             ModelFK,
  19.             Frame,
  20.             PowerRating,
  21.             Volts,
  22.             Rmin,
  23.             Amps,
  24.             Ph,
  25.             ImpellerDetails,
  26.             Hz,
  27.             ExRated,
  28.             AssetNotes,
  29.             QuoteP,
  30.             QuotePDate,
  31.             Price,
  32.             QuoteAccepted,
  33.             QuoteACDate,
  34.             TargetCDate,
  35.             UQuoteNotes,
  36.             ActualCDate,
  37.             DeliveryDate,
  38.             FailureReasonFK,
  39.             FRNotes,
  40.             RSLoggedDate,
  41.             RSInspectDate,
  42.             InspectedByFK,
  43.             RSQuotedDate,
  44.             RSApprovedDate,
  45.             RSWIPDate,
  46.             AssignedToFK,
  47.             RSDelDate,
  48.             RSCompDate
  49.     from        tblWorkshops
  50.     where        1=1
  51.             <cfif #form.csearch# is not "">
  52.                    and ClientWONO like '%#form.csearch#%'
  53.                 </cfif>
  54.  
And this is the update query code:

Expand|Select|Wrap|Line Numbers
  1. <cftransaction>
  2.  
  3. <cfparam name="form.ExRated" default="0">
  4. <cfparam name="form.QuoteP" default="0">
  5. <cfparam name="form.QuoteAccepted" default="0">
  6.  
  7.  
  8. <cfquery name="updatedatabase" datasource="repairmdb">
  9.  
  10. update     tblWorkshops
  11. set    WorkshopFK = '#form.WorkshopFK#',    
  12.     ClientFK = '#form.ClientFK#',
  13.     SiteFK = '#form.SiteFK#',
  14.     WORecdate = #form.WORecdate#,
  15.     ResponseLevelFK = '#form.ResponseLevelFK#',
  16.     ClientWONO = '#form.ClientWONO#',
  17.     WorkshopWONO = '#form.WorkshopWONO#',
  18.     WorkshopJOBNO = '#form.WorkshopJOBNO#',
  19.     AssetNO = '#form.AssetNO#',
  20.     AssetClassFK = '#form.AssetClassFK#',
  21.     LocalID = '#form.LocalID#',
  22.     ManufacturerFK = '#form.ManufacturerFK#',
  23.     SerialNO = '#form.SerialNO#',
  24.     BuildYear = '#form.BuildYear#',
  25.     ModelFK ='#form.ModelFK#',
  26.     Frame = '#form.Frame#',
  27.     PowerRating = '#form.PowerRating#',
  28.     Volts = '#form.Volts#',
  29.     Rmin = '#form.Rmin#',
  30.     Amps = '#form.Amps#',
  31.     Ph = '#form.Ph#',
  32.     ImpellerDetails = '#form.ImpellerDetails#',
  33.     Hz = '#form.Hz#',
  34.     ExRated = #form.ExRated#,
  35.     AssetNotes = '#form.AssetNotes#',
  36.     QuoteP = #form.QuoteP#,
  37.     QuotePDate = '#form.QuotePDate#',
  38.     Price = '#form.Price#',
  39.     QuoteAccepted = #form.QuoteAccepted#,
  40.     QuoteACDate = '#form.QuoteACDate#',
  41.     TargetCDate = '#form.TargetCDate#',
  42.     UQuoteNotes = '#form.UQuoteNotes#',
  43.     ActualCDate = '#form.ActualCDate#',
  44.     DeliveryDate = '#form.DeliveryDate#',
  45.     FailureReasonFK = '#form.FailureReasonFK#',
  46.     FRNotes = '#form.FRNotes#',
  47.     RSLoggedDate = '#form.RSLoggedDate#',
  48.     RSInspectDate = '#form.RSInspectDate#',
  49.     InspectedByFK = '#form.InspectedByFK#',
  50.     RSQuotedDate = '#form.RSQuotedDate#',
  51.     RSApprovedDate = '#form.RSApprovedDate#',
  52.     RSWIPDate = '#form.RSWIPDate#',
  53.     AssignedToFK = '#form.AssignedToFK#',
  54.     RSDelDate = '#form.RSDelDate#',
  55.     RSCompDate = '#form.RSCompDate#'
  56. where     ID = #form.ID#
  57.  
  58. </cfquery>
  59. </cftransaction>
  60.  
  61.  
Not sure if it helps, but our ancient servers are running CF5.

Can't see what i am donig wrong for the date value to be changed in Access to 30/12/1899.

Thanks
Neil
Oct 6 '08 #7
ndeeley
139 100+
Acoder,

Its okay - I have fixed it. My mistake(Doh!) - I was passing the update value of the date as a number.

The smallest thing, eh?
Thanks for your help.
Neil xxxxxxxxxxxxxx
Oct 6 '08 #8
acoder
16,027 Expert Mod 8TB
Yes, it usually is the smallest of things causing all the problem.

Glad you managed to solve it.
Oct 6 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: PG | last post by:
When deleting a row from the database, that id is now missing. So what I'm trying to do is update in a loop (maybe an sql loop if there is one) of all of the id numbers - sort of like renaming...
2
by: deko | last post by:
I have an unbound text box on a form that is set to: Format = Short Date (with the corresponding input mask) When I run this: DoCmd.RunSQL ("UPDATE tblOutput SET ApptStart = " &...
11
by: Miikka Hamalainen | last post by:
Hi All, I'm making library db using SQL. I have date when book has been loaned (Date loaned) and date to return the book (Return date), which is 4 weeks. How can I get current date to show...
5
by: m_t_hill | last post by:
Running MS Access 2000 MS Windows XP Pro This has caused me a lot of hair loss in the last few days so would appreciate any help. I am running code to append/update a local access database...
1
by: Riley DeWiley | last post by:
I have an UPDATE query that is always setting 0 records. When I cut and paste the SQL into Access and use it, it fails in the same way unless I coerce the date fields to be '=now()', in which case...
11
by: Dixie | last post by:
How can I programatically, take some Date/Time fields present in a table in the current database and change their type to text? dixie
1
by: Tony | last post by:
Hi, I have two forms A and B, both opened. In form A, I programmatically change the Date of Birth field of the current record of form B. I noticed that form B automatically displays the new data...
7
by: Jerome | last post by:
Hallo, I know a lot has already been told about date/time fields in a database but still confuses me, specif when dealing with SQLserver(Express). It seems that sqlserver only accepts the date in...
3
by: perryche | last post by:
All, In excel, when you find a field within a spreadsheet is wrong, and every row needs to be changed, you simply change one cell, copy that cell, and select all the rest of the cell, and paste...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.