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
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
Use cfqueryparam for the data input and set the CFSQLType attribute to use the CF_SQL_DATE type.
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
It has to be within the query, so that would be the action page.
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!)
No problem. If you get stuck, post your code and I'll see what I can do.
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: -
-
<cfquery datasource="repairmdb" name="GetClientNo">
-
select ID,
-
WorkshopFK,
-
ClientFK,
-
SiteFK,
-
WORecdate,
-
ResponseLevelFK,
-
ClientWONO,
-
WorkshopWONO,
-
WorkshopJOBNO,
-
AssetNO,
-
AssetClassFK,
-
LocalID,
-
ManufacturerFK,
-
SerialNO,
-
BuildYear,
-
ModelFK,
-
Frame,
-
PowerRating,
-
Volts,
-
Rmin,
-
Amps,
-
Ph,
-
ImpellerDetails,
-
Hz,
-
ExRated,
-
AssetNotes,
-
QuoteP,
-
QuotePDate,
-
Price,
-
QuoteAccepted,
-
QuoteACDate,
-
TargetCDate,
-
UQuoteNotes,
-
ActualCDate,
-
DeliveryDate,
-
FailureReasonFK,
-
FRNotes,
-
RSLoggedDate,
-
RSInspectDate,
-
InspectedByFK,
-
RSQuotedDate,
-
RSApprovedDate,
-
RSWIPDate,
-
AssignedToFK,
-
RSDelDate,
-
RSCompDate
-
from tblWorkshops
-
where 1=1
-
<cfif #form.csearch# is not "">
-
and ClientWONO like '%#form.csearch#%'
-
</cfif>
-
And this is the update query code: -
<cftransaction>
-
-
<cfparam name="form.ExRated" default="0">
-
<cfparam name="form.QuoteP" default="0">
-
<cfparam name="form.QuoteAccepted" default="0">
-
-
-
<cfquery name="updatedatabase" datasource="repairmdb">
-
-
update tblWorkshops
-
set WorkshopFK = '#form.WorkshopFK#',
-
ClientFK = '#form.ClientFK#',
-
SiteFK = '#form.SiteFK#',
-
WORecdate = #form.WORecdate#,
-
ResponseLevelFK = '#form.ResponseLevelFK#',
-
ClientWONO = '#form.ClientWONO#',
-
WorkshopWONO = '#form.WorkshopWONO#',
-
WorkshopJOBNO = '#form.WorkshopJOBNO#',
-
AssetNO = '#form.AssetNO#',
-
AssetClassFK = '#form.AssetClassFK#',
-
LocalID = '#form.LocalID#',
-
ManufacturerFK = '#form.ManufacturerFK#',
-
SerialNO = '#form.SerialNO#',
-
BuildYear = '#form.BuildYear#',
-
ModelFK ='#form.ModelFK#',
-
Frame = '#form.Frame#',
-
PowerRating = '#form.PowerRating#',
-
Volts = '#form.Volts#',
-
Rmin = '#form.Rmin#',
-
Amps = '#form.Amps#',
-
Ph = '#form.Ph#',
-
ImpellerDetails = '#form.ImpellerDetails#',
-
Hz = '#form.Hz#',
-
ExRated = #form.ExRated#,
-
AssetNotes = '#form.AssetNotes#',
-
QuoteP = #form.QuoteP#,
-
QuotePDate = '#form.QuotePDate#',
-
Price = '#form.Price#',
-
QuoteAccepted = #form.QuoteAccepted#,
-
QuoteACDate = '#form.QuoteACDate#',
-
TargetCDate = '#form.TargetCDate#',
-
UQuoteNotes = '#form.UQuoteNotes#',
-
ActualCDate = '#form.ActualCDate#',
-
DeliveryDate = '#form.DeliveryDate#',
-
FailureReasonFK = '#form.FailureReasonFK#',
-
FRNotes = '#form.FRNotes#',
-
RSLoggedDate = '#form.RSLoggedDate#',
-
RSInspectDate = '#form.RSInspectDate#',
-
InspectedByFK = '#form.InspectedByFK#',
-
RSQuotedDate = '#form.RSQuotedDate#',
-
RSApprovedDate = '#form.RSApprovedDate#',
-
RSWIPDate = '#form.RSWIPDate#',
-
AssignedToFK = '#form.AssignedToFK#',
-
RSDelDate = '#form.RSDelDate#',
-
RSCompDate = '#form.RSCompDate#'
-
where ID = #form.ID#
-
-
</cfquery>
-
</cftransaction>
-
-
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
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
Yes, it usually is the smallest of things causing all the problem.
Glad you managed to solve it.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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 = " &...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |