SQL query

[Ankarn]

strSql = "INSERT INTO Masterinfo (Masternr, Masterkode, Målartype, Betegnelse, Fabrikat, Spenning, Pris, Fase) VALUES (" & Me.Masternr & ", " & "'" + Nz(Me.Masterkode, "Null") + "'" & ", '" & Me.Mtype & "', " & "'" + Nz(Me.Betegnelse, "") + "'" & ", " & "'" + Nz(Me.Fabrikat, "") + "'" & ", " & "'" + Me.Spenning + "'" & ", " & Me.Pris & ", " & "'" + Me.Fase + "'" & ");"

strSql = "INSERT INTO Masterinfo (Masternr, Masterkode, Målartype, Betegnelse, Fabrikat, Spenning, Pris, Fase) VALUES (" & Me.Masternr & ", Null, '" & Me.Mtype & "', Null, Null, Null, Null, Null);"

The first one doesnt work, the second one does.

I want to be able to not have any values in Me.Fabrikat and Me.Spenning and such.

If i want it to be optional, what do i do in the SQL-statement?

 

[LPurvis]

Hi.

It seems to me you're already aware of how. ;-)
In your second field VALUE you've implemented
Nz(Me.Masterkode, "Null")
which will pass the literal string "Null" into your SQL statement if the Masterkode control is Null - which is exactly what you want.
You need to implement it for other fields you want to be Nullable too though.

As a caveat for something you might come unstuck against - your delimitation.
& "'" + Nz(ControlName, "Null") + "'" &
will not get you what you want - you don't want to pass 'Null' into your string as text.
I'd suggest you perform all such delimitation within the Nz function call.
& Nz("'" + ControlName + "'", "Null") &
Then the propagation of Null will behave as you're needing it to.

See how you get on. Post back if it's not going well.

 

[Ankarn]

Thats the goodstuff. I was looking for something excactly like that. I didnt find a way to only include the ' ' if Me.Controll was not null. Ive spent so much time on this its ridiculeos.

Thanks man.

 

[namliam]

General remarks:
- Dont use + to concatinate texts, + is for adding numbers, it works on concatinations to but it is implicit use of the + sign. Use & only for concatinations.

- Why insert a literal "NULL" into a field to imply an empty field, an avareage user will not understand that.... better to use "" IMHO??

- It is not advisable to use special characters in column names (Målartype) not all databases support that and may cause you trouble in the future

 

[Ankarn]

I am aware of the å. But its a norwegian letter and this is to be used for norwegian. I try to use those letters as little as possible.

I didnt want to enter the string "Null". I know that doesnt make sense. I wanted to add Null.

As i understand, + is to use if both sides has to be something for it to equal something.
Like Anything + nothing = Nothing, and Anything & Nothing = Anything.

 

[LPurvis]

Just to re-affirm some things Ankarn that namliam raises and may cause you concern. I'll address the points one by one.

- IMO your use of + for concatenation is appropriate here for the given implementation. Using both + and & for string concatenation is a pretty standard way of controlling Null propagation for desired effect.

- The intent with this code is not to be inserting any literal "Null" - but we do have to insert a Null string into the SQL to render the final statement with Null values. It should be doing exactly that now (and AFAICT was always the intent).

- I agree that as standard (internationally too) is best for naming conventions. If nothing else - it could hinder the ability for you to receive international help one day. ;-)

Cheers.

 

[LPurvis]

>> Like Anything + nothing = Nothing, and Anything & Nothing = Anything.

Effectively yes - it's referred to as propagation of Null.
Anything + Null => Null , and Anything & Null => Anything
The + operator propagates Null as most operators do - simply because a comparison with Null always results in Null (which is why we have functions like IsNull and Nz - and the Is Null SQL comparison... because = Null always results in Null).
Ampersand concatenation in VBA, Access and Jet expressions conatenates irrespective of Nulls.
Compared to environments, say in SQL Server, where we concatenate only with + and have to handle Nulls explicitly lest they propagate without intention. (But Jet does a lot of that type of hand holding which T-SQL doesn't.. it's just the way its designed, and it has real uses and advantages too .
There's a definitive piece on the subject by me awlde mate here http://www.utteraccess.com/forums/showflat.php?Cat=&Board=53&Number=1553733

 

[namliam]

Effectively yes - it's referred to as propagation of Null.
Anything + Null => Null , and Anything & Null => Anything

This is right but also supports my position that + does not equal &. Concatination should be done by using & only (IMO). In concatination you want/need Anything & Null = Anything, you want them pasted after eachother.
Besides anything else, mixing things like this is just plain ugly and messy.

About the special Characters, I understand it is Norwegian but that is no excuse to use it in your design. What is inside the database is hidden from the user anyway, so why have it there? using any characters on forms/reports etc that are or become visible to a user, yes you can and must do that. But not inside your DB.

 

[LPurvis]

I just can't agree with that. A huge number of developers take advatange of controlled propagation of Null by employing both + and & as appropriate.
Things like this are about training one's brain I find.
As with quotes in a string - some swear that
"SomeField = " & Chr(34) & "YourVal" & Chr(34)
is the way to go - purely for clarity.
I don't mind it - but I can perfectly equally read
"SomeField = ""YourVal"""

So by comparison take the string
& Nz("'" + ControlName + "'", "Null") &
to use ampersands only - where do we go with this (while still as a single expression...)
& IIF(IsNull(ControlName), "Null", "'" & ControlName & "'") &
From a logic point of view that may seem clearer. To those used to staring at propagated Null expressions the former jumps out at least as clearly - but with greater brevity.
It's very commonly used. And IMO is not a poor practice.

As to the characters in names issue - having never developed in any language other than my native English, I'll leave that to others to debate.

 

[namliam]

I guess it is a patato patato thing... And surely it has its uses, but generaly.... I still say use the & for concatination because of its more "explicit"/expected behaviour.

To me when concatinating strings "Anything" + Null = Null just doesnt make sence. Offcourse if that is exactly what you need... but I have not had the "pleasure" of needing that at all.

 

[Ankarn]

Can i ask you SQL wizards one more question?

I have this SQL

strSql = "UPDATE Query1 SET OrdreID = " & Me.NyOrdrenr & ", MottakerID = " & DLookup("[MottakerID]", "Mottaker", "Mottaker = '" & DLookup("[Mottaker]", "Ordrer", "OrdreID = " & Me.NyOrdrenr) & "'") & " WHERE Malernr = " & Me.Malernr

Which delete records from query1.
Why is that? Should UPDATE delete records from a query? I have used OrdreID as a criteria to produce the Query, but once the Query1 is there, i should be able to do whatever i want with it without it losing records?

 

[namliam]

If you are actually changing the fields that you have selected on offcourse it will disappear from your query..

I.e. I query all records with ID < 10 from this table
1
2
3
4
5
12
13
Obviously selecting 1,2,3,4,5 but if I now update 1 to 11... that will obviously drop out of scope for the query...

strSql = "UPDATE Query1 SET OrdreID = " & Me.NyOrdrenr & _
", MottakerID = " & DLookup("[MottakerID]", "Mottaker", "Mottaker = '" & _
                    DLookup("[Mottaker]", "Ordrer", "OrdreID = " & Me.NyOrdrenr) & "'") & _ 
" WHERE Malernr = " & Me.Malernr

I dont quite understand the logic of this update query tho...
You do 2 DLookups (probably redundant):
1) Get the Mottaker (name?) from the Order table
2) Find that Mottaker (name?) in the Mottaker table (Should really be tblMottaker!)
Then updating it's ID into Query1 (very bad name, I hope you are simply testing) which is I think based of the Odrer (tblOdrer) table.
Then updating the MottakerID and OrdreID.
1) Why update the OrdreID? Should be your PK right?? So why change it if it is also part of your DLookup search criteria?
2) If you have the Mottaker (name?) on your form allready, presumably from a combo or something? Why not retrieve it's ID from there as well.
3) DLookups are Soooooooooooo Sloooooooooooooow. You are probably better of using a recordset instead.

 

[Ankarn]

Ah.. ok. So it will drop out.

I dont want it to drop out, thats the thing. Because my form is displaying all the query1 records, and when i change it, i still want it to show. Query 1 is a table that changes all the time, so i just want it in there till the next search.

Isn't it possible to still have them still displayd?

 

[namliam]

It is possible, but you would have to change query1 to also show the new OrdreID.

Still IDs should not change IMHO

 

[Ankarn]

ID is not the primary key. OrdreID is primary in the table Ordrer but not primary in the Query1, so it is ok to change it. It is serialnumbers that changes ordreID.

But the new OrdreID isnt a field, so how do i implement that? I would have to build the Query1 from the start after i have selected the new ordrenr, right?

Just so we understand each other. I can look up OrdreID = 16 and get 15 hits. Lets say i want to change 4 of them to OrdreID = 14, whare there allready is 10 records. Now there are 11 hits on OrdreID 16 and 14 hits on ordreID 14.

I still just want the originally 16 to be displayd.

 

[namliam]

OrdreID is on the form then? In NyOrdrenr, if it is a foreign key yes it is OK to change it.

Then again, why use a seperate update query? And not do it using the form or something. After all you are searching for all "Mottaker" that match the NyOrdrenr (= New?OrdreNR?) to update them to the NyOrdrenr ??

I cannot really see a way to do what you want without a lot of trickery...
It is funny tho, you do have to refresh the query to display the proper data... the 1 in my example stays 1 untill you requery it to show 11.

Does your Ordrer table have a PK of its own?? If so you can store the PK's displayed in a variable and add them to the query criteria to be displayed in the second "version" showing OrdreID 14 instead of 16.

 

[Ankarn]

I search for a given ordrenr, and i want to change it to another, when i do that, it will disappear from the form where i'm displaying it. I suspect it would take a lot of trickery. My Ordretable does have a PK. In my other table "Lagerdata" i have a lot of elements, and there several elements kan have the same PK.

The problem is that when i change the OrdreID i dont want the Query-filter to influence the displayd form anymore.

 

[namliam]

Well then use the form's current recordset to fetch all the PK's from your Query1.
Then alter then form filter to display on the Odre number, but the PKs you want it to show, including your Odre 14 instead of 16.