tonialbrown
Last Activity: Feb 1 '08, 02:38 AM
Joined: Jan 10 '08
Location:
-
I was unable to open the attached file as it is in a later version of Access. I am only using Access 2000 so perhaps that is the difference to why some things are not working.... -
Thankyou, thankyou, thankyou....
I changed the line to use the chr(35) and got the same result. Is the below what you meant???
"[delegation].[on] = " & IIf(Not IsNull(rst.Fiel ds(9)), "#" & rst.Fields(9).V alue & "#", "'" & Chr(35) & Chr(35) & "'") & "," & _
With the format for the dates, I did the following - just...Leave a comment:
-
I had a play around with what you gave me and almost got it to work!
strDateCriteria = IIf(Not IsNull(rst.Fiel ds(9)), "#" & rst.Fields(9).V alue & "#", "'##'")
"[delegation].[on] = " & strDateCriteria & _
then further streamlined by getting rid of the strDateCriteria field to this:
"[delegation].[on] = " & IIf(Not IsNull(rst.Fiel ds(9)),...Leave a comment:
-
Thanks I tried it.
The first way does not work for the nulls (only for dates) and comes up the sql date syntax error as before (basically same as before).
The Sql from the immediate window ends up being
update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Kay Kell',[delegation].[title] = 'Service Manager (Child, Family & Youth)',[delegation].[section] = 'Community...Leave a comment:
-
Thanks Jim for clarifying this. I have actually changed my Nz statement to the get the values from a recordset rather than getting the values from a list box and it seems to work with the following:
"[delegation].[keywords] = '" & Nz(rst.Fields(6 ).Value, " ") & "'," & _
"[delegation].[adopted] = '" & Nz(rst.Fields(8 ).Value, " ") & "'," & _...Leave a comment:
-
After changing the line - the error is still 'Syntax error in date in query expression '# #'
The line from the immediate window (when a NULL value in [delegation].[on]) is:
update delegation set [delegation].[seg] = ' ',[delegation].[other] = ' ',[delegation].[name] = 'Pam Barber',[delegation].[title] = 'Supply Co ordinator',[delegation].[section] = 'Finance',[delegation].[keywords] = 'Support Services
Support...Leave a comment:
-
For some reason everytime I add this line it changes my code from below:
sqlupdate = "update delegation " & _
"set [delegation].[seg] = '" & Nz(rst.Fields(1 ).Value, " ") & "'," & _
"[delegation].[other] = '" & Nz(rst.Fields(2 ).Value, " ") & "'," & _
"[delegation].[name] = '" & Nz(rst.Fields(3 ).Value,...Leave a comment:
-
The date is formatted as a Date/Time field in the table (short date).
I tried what you suggested but get a "invalid use of null" when the field I am trying to copy is null.
When I copy a field that has a date in it now - say 5/05/2007 when it places the data in the new field it now becomes the date 30/12/1899
Previously when I used
'"[delegation].[on] = #" & Nz(rst.Fields(9 ).Value,...Leave a comment:
-
Many thanks for your suggestion, I did try this but unfortunately I get a 'SYNTAX ERROR IN UPDATE STATEMENT'
I have also tried the following:
"[delegation].[on] = #" & Nz(rst.Fields(9 ).Value, "") & "#" & _
The above line works if the field is not null that I am copying across, if it is null it does not work.
So I tried to change the way I was doing...Leave a comment:
-
How to update a date field from another records date
I have an Sql update statement that I am using that updates the data from a record chosen by the user from a list box lstDelFrom.
This is working for all the text fields & updates fine. Once I add in one of the fields that is a Date field I cannot get the update to work.
ie) this line "[delegation].[on] = #" & Nz(rst.Fields(9 ).Value, "") & "#," & _
Could someone assist me... -
Null values being generated from list box - can't understand why
I am having some problems with an update statement. The problem is the data comes from a list box lstDelFrom that the user selects for the required record that they are copying the data from. This list box is generated from a query that selects information from the delegation table only.See below code.
SQL = "update delegation " & _
"Set [delegation].[seg] = '" & IIf(lstDelFrom. Column(1) = "",... -
Thanks for the replies and pointing me in the right direction, the following ended up working:
IIf(lstDelFrom. Column(3) = "", "NA", lstDelFrom.Colu mn(3))
as the value ended up being a blank rather than a null value....Leave a comment:
-
Thanks I will have a look and see if I can incorporate in my code....Leave a comment:
-
Thanks for your response, I did try the above but was not able to get it to work. The fields are Text fields but I am wondering if the data is actually a NULL value or not. What it is, is a field where no data entry has occurred against some of the items. In all cases there is something entered in title and usually name.
I am using a List box lstDelFrom which queries a table to get the information. Perhaps the problem occurs at the...Leave a comment:
-
How to Handle Null values in Sql update statment
I have some code that updates a record's fields based on a selection from a list box (lstDelFrom). The user selects the record from the list box & it copies it into the fields [seg], [other], [name] etc.
My problem is that the code only works if all the fields that are being copied from have information in them. It does not work when some of the fields are null such as [seg] & [other] which is often the case.
... -
Sorry not quite clear on what you mean. Do I list each field separately in the append query as similar to how I am doing it the update query or do I do something else. If I am allowing the user to choose the ID/Name/Title from the list box do I copy these in from the list box??
Thanks....Leave a comment:
-
Sorry not quite clear on what you mean. Do I list each field separately in the append query as indicated in the update query or do I do something else.
Thanks....Leave a comment:
-
Many thanks for your reply.
The number of fields each record has is 15. But the way I was doing it I only had 3 of the fields making up the list box - ie) the id, title & name where the user picked the record to copy from.
When you say steal the SQL text - do you mean from the update in my code above???
Do I then list each of the fields that I am adding from the text box ie) similar to the following:...Leave a comment:
-
How to copy multiple fields from one record to an existing record
I was hoping that someone may give me some help in regards to writing some code.
I have the following code that works - basically a user selects a record from a list box & it copies the fields name & title into the new record with the key(id) already assigned.
What I want to be able to do is the same idea but copy all fields from the selected user record into the new record (except for the id). Obviously the...
No activity results to display
Leave a comment: