maybe I'm more of a web developer than a database dev? soon i'll be developing my first mobile app for android. so before i know it my nickname will be "swifty". does anyone follow that joke?
you got me. I got my platforms mixed up! But actually, I told this employer that I did find a way to dev iOS mobile apps on a windows machine. but per Apple, that is illegal to do so it's prolly risky to perform such a development project. But I'm sure if I do android, eventually i'll have to do iOS as well.
If you mean not letting users have direct access to tables, nobody here would disagree. You realize data macros run when data is manipulated in a form, right?
this thread has gotten way off topic you guys. perhaps it's best to take the discussion elsewhere. as far as data macros go, just forget what I said if you're confused.
If alternative value not provided for Nz() seems Access will assign one as circumstances dictate.
In immediate window:
?0=Nz(Null)
True
?""=Nz(Null)
True
Whilst I agree with the last sentence, I'm not convinced the tests are meaningful.
A better test would be a query using the Nz function without specified values on fields of different data types. The attached test includes text, number and date fields.
The query used was
Code:
SELECT Table1.ID, Table1.TField, Nz([TField]) AS Expr1, Table1.NField, Nz([NField]) AS Expr2, Table1.DField, Nz([DField]) AS Expr3
FROM Table1;
As you can see there is no difference in what is displayed for nulls in the 3 test fields
Which would you rather have typed? And which one reads easier?
Sometimes those functions are just "another way to skin a cat." {MEEEOOOWRRR } The point of functions like NZ is simply this: They are tools in the toolkit. If you don't have a lot of tools in your toolkit, you will eventually run across a situation where the thing you are hammering on had better be a nail 'cause all you could find was a hammer.
As to the Dxxx functions? Just remember that VBA doesn't look at records unless you opened a recordset for it. The DLOOKUP function just saves you the trouble of allocating a recordset variable, opening it, doing a .FindFirst, retrieving the value, and closing the recordset. That just got encapsulated into the DLOOKUP function. Which would you rather write? A one-off recordset sequence or a DLOOKUP?
Honestly, despite my experience, there are virtually TONS of tricks in Office related to Access, VBA, and application objects. Hey, I'm sometimes overconfident, but in this case I am ABSOLUTELY confident when I say I don't know HALF of all the things one can do with Access.
Where I have any expertise is that my experience as a PhD and as a Navy Sys Admin is, I know how to frame the questions in order to look up things. But even there, between 5% and 10% of the time, I have to re-frame the question more than once to find stuff.
So file NZ() function as just another tool in the toolkit. A tool that ISN'T so blunt as a hammer.
The point of functions like NZ is simply this: They are tools in the toolkit. If you don't have a lot of tools in your toolkit, you will eventually run across a situation where the thing you are hammering on had better be a nail 'cause all you could find was a hammer.
It appears Access has a lot of "tools" in their toolkit, I have seen videos put out by the Microsoft Access teams demonstrating how to use not only calculated fields but data macros, Yes/No fields and lookup wizards. Are these tools designed for the lazy programmer? Are they the Harbor Freight of Access Tools.
It appears Access has a lot of "tools" in their toolbox, I have seen videos put out by the Microsoft Access teams demonstrating how to use not only calculated fields but data macros, Yes/No fields and lookup wizards. Are these tools designed for the lazy programmer? Are they the Harbor Freight of Access Tools.
I would say Access was designed for Power Users - not necessarily for programmers. Just like Word, Excel, PowerPoint, and Outlook have VBA languages but the average user won't ever probably use them.
Oh, heck, I ALWAYS use the form and control wizards for events. Sure, the code they generate is primitive - but it is linked correctly and just WAITING for me to get in there and customize the crud out of it. I look at like the wizards provide the scaffolding but it is up to me to put in the body and the finishing touches.
I have seen videos put out by the Microsoft Access teams demonstrating how to use not only calculated fields but data macros, Yes/No fields and lookup wizards. Are these tools designed for the lazy programmer?
I have read many experts say that lookup fields create problems and they stress not to use them. As far as wizards are concerned, I don't believe the web languages have these kinds of things, do they? If they do, I've never used one.
AB
You missed out Attachment fields from your list.
You did include boolean (Yes/No) fields. Any reason why? They are a standard tool in the armoury of most developers
AB
You missed out Attachment fields from your list.
You did include boolean (Yes/No) fields. Any reason why? They are a standard tool in the armoury of most developers
This isn't the only reference to them I have seen over the years. http://allenbrowne.com/casu-23.html And yes they seem to be a staple in the toolkit, I use them. I also use button macros to advance or previous records. Sometimes opting to convert them on a later date.
I agree that Yes/No fields shouldn't be used in that way (though I've done so myself in the past) as it leads to denormalised data.
There is another reason why they can be problematic. See http://allenbrowne.com/bug-14.html and http://allenbrowne.com/NoYesNo.html
But there are also plenty of valid reasons for using them.
That's why I think they are different to attachment, calculated & multivalued data types and to lookup fields at table level.
I no longer use wizards or macros for anything (other than autoexec & autokeys macros) ... but that's just a personal preference
Oh, heck, I ALWAYS use the form and control wizards for events. Sure, the code they generate is primitive - but it is linked correctly and just WAITING for me to get in there and customize the crud out of it. I look at like the wizards provide the scaffolding but it is up to me to put in the body and the finishing touches.
I use the reports wizard frequently, they often point out headers, footers and grouping in ways I may not have considered while trying to solve a particular issue. Then simply adapt the results to my situation. I also borrow normalized template tables and adapt them. Saves time for non professional developers.
Unfortunately this common misconception has been promulgated by Microsoft in their explanation of Nz().
Microsoft said:
If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string.
A function does not have the facility to return a value based on the context of the call. What you see is cast according to the context from the Variant that is returned by Nz().
Here is the definitive test of the returned datatype of Nz().
Code:
? TypeName(Nz(Null))
Empty is a Variant without data. If you include the optional second parameter the Variant will be interpreted according to the datatype of that value then cast to the context of the call.
does anyone follow that joke?
