Statement Interpretation [ ] ! [ ], [ ] . [ ] and CDbl (1 Viewer)

[stanhook]

HI,

I am trying to work with an access 2007 database and update a website. I have a bunch of queries I need to interpret and bring them up to date. Can someone please tell me what this means:

CDbl(IIf(IsNumeric( [tblSU]![SUNum]),[tblSU]![SUNum],
Left([tblSU]![SUNum],
  InStr(1,[tblSU]![SUNum],'-')-1)
)
)

I am new to this and found that CDbl means to convert a value to a double which still make very little sense to me even with the examples I have found. It also seems that the Left command returns a specified number of characters from the left side of a string. A Little more clear but I am still not sure what this is trying to accomplish. This is even more confusing - [tblSU]![SUNum]),[tblSU]![SUNum] If I could know what the statement is saying I hopefully would be able to see what I might be able to do moving forward. I have a bunch but this is a start.

This is an example of another one:

IIf([tblmap].[mapelec],'Digital Maps',IIf([tblmap].[mappaper],'Paper Maps','Other Maps'))

I know that it is saying something like evaluate this and if it is true then assign it this value. Again it is this - [tblmap].[mapelec] that is giving me the trouble.

Thanks for the help!

 

[Gasman]

I will take a stab st it.

Work from the inside out.
If tblSU]![SUNum] is numeric, then use that field, if not find the - character and take everything before that (presumably the numeric part).
Then convert it to Double.

If [tblmap].[mapelec] is True (or not zero), set to Digital Maps, else if if not and [tblmap].[mappaper] is True (or non zero) then set to Paper Maps, else Other Maps.

 

[June7]

I will guess that [mapelec] and [mappaper] are Yes/No fields in [tblMap] so the fields will evaluate as True or False in the IIf() expression.

 

[MajP]

Not sure what part of this you do not understand. By the title it looks like you have some quetions on [], !, and "."
There are some differences depending if it is pure SQL or uses a function in the SQL like (iif, or Cdbl).

To best describe here is a simple table
tblOne
-- ID
-- Desc
-- StartDate

To do a simple query

Select ID, Desc, StartDate from TblOne

1. If my name has a space, is a reserved word, or uses illegal characters have to enclose with [] to make sure it can interpret as a name. But you can always use them to be safe.

Select [User ID], [Date], [#Flight], Desc from [Table One]

2. If the field is used in a function such as "Year", "iif", "cdbl', need the []

Select Year([StartDate]) from tblOne

3. The table name is optional. You only really need to include the table name when you have a join and both tables have the same fieldname. The table name is then needed to know which one you are referring to. In access SQL you normally do
TableName.FieldName
I would convert all to use period. But
TableName!FieldName works too.

Select tblOne.ID, [table two].ID, tblOne!StartDate, tblOne![Field With Space]
From TableOne
Inner Join [table two]
On tableOne.ID = [table two].ID

4. When referring to controls and forms you have to use ! even if it works with '." in vba

Select ID, Desc from tblOne where id = [Forms]![formOne]![txtBoxID]

Your examples also have examples of iif functions. Look here for a simpler example

IIf function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

The issue with iif is often (unlike the simple example) you do not return a simple value but you return another function.
In your case it then used another function that uses the left function and the instr function

InStr function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Left

Left function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

As @Gasman pointed out function are often nested so you have to work from the inside out. In your first example you have an instr, nested in a left, nested in an iif, nested in a Cdbl. So you need to learn each individually first.

 

[CJ_London]

Look at some data. I would hazard a guess that [tblSU]![SUNum] is text and from the formula stores either something like ‘12.34’ or ‘12.34-abc’

The formula is saying 'if [tblSU]![SUNum]=‘12.34’ then use 12.34, otherwise use the values to the left of the - character'

Note that the function will error if the value is not a number or does not contain a - character preceded by a number

Pretty sure the same result could have been achieved much more simply with

Val([SUNum])

which evaluates all the numeric characters before a non numeric character

 

[stanhook]

Thank you all so much!! This has been extremely helpful for me being new to all of this.

One thing, the '!' and '.', it is simply being used because it is being used in control or form? In my case it is a form that is referencing the statement. Do they have any other meaning?

CDbl - Convert to a Double - Is that literal? Double the value? Why use it?

I think I am starting to understand and will look at the docs to see if I can get more clarification. Any other input is appreciated as it is helping me to learn. Docs only get me so far and hearing from real world examples and explanations help a lot!

This is awesome and you all were very helpful!!
Stan

 

[Gasman]

CDbl - Convert to a Double - Is that literal? Double the value? Why use it?

No, it converts it to Double Type numeric

Data type summary

Office VBA reference topic

learn.microsoft.com

 

[MajP]

CDbl - Convert to a Double - Is that literal? Double the value? Why use it?

If you have not figured it out from the examples so far you can answer these questions by simply spending some time to look up any vba function on learn.Microsoft.com

Type conversion functions (VBA)

Office VBA reference topic

learn.microsoft.com

 

[The_Doc_Man]

The difference between "bang" (!) and "dot" (.) has to do with interpretation of the right-hand side of that delimiter - not only how, but when.

The DOT is used as a separator when the item to the right of the dot is (believed to be) a member of the collection of things associated with the item to the left of the dot. Dot usage enables a VBA-related feature called "Intellisense" that looks AS YOU TYPE for the right-side item among the collection of properties and members of the left-side item. VBA's editor will start making suggestions if it finds something.

For example, suppose you have a textbox named Details and you are programming some VBA code to change the displayed appearance of Details. In your code line you type Details. and then immediately follow that with the letters "bo". At this point, because the dot triggered Intellisense, you might see Details.bordercolor as a suggestion because that is the first property (alphabetically) to match what you have typed in so far. You can use the shortcut Me to refer to the form and then use the DOT to get to things associated with it. Like Me.Details - because the controls on the form can be located easily and the dot as a separator implies that association.

The BANG is used as a separator very much like DOT in some ways - but the way it is interpreted is different. It represents a runtime operator whereas DOT is a compile-time operator. Our member MajP has written several articles on this subject and they may be of use. He is more eloquent than I on this subject so I will offer a link:

Lets Kill the Me. vs Me! sticky or lets rewrite it

This discussion moved to clean thread https://www.access-programmers.co.uk/forums/threads/bang-vs-dot-me-and-default-properties.327749/

www.access-programmers.co.uk

As to deciphering your expression, let's do this piecemeal. You have already gotten excellent advice but perhaps you need pointers on how to approach other similar problems. You started with

CDbl(IIf(IsNumeric( [tblSU]![SUNum]),[tblSU]![SUNum],Left([tblSU]![SUNum], InStr(1,[tblSU]![SUNum],'-')-1)))

The correct way to do this is to break it apart into the many layers involved. I will break it apart for you.

CDbl( IIf( IsNumeric( [tblSU]![SUNum]), [tblSU]![SUNum], Left([tblSU]![SUNum], InStr(1,[tblSU]![SUNum],'-') -1 ) ) )

From the inside out, you have the InStr function (orange) which is a function to find the location of a character in a longer string. It returns a character position so that you could use it to say "the dash character ( '-' ) is the 6th character of the string. I.e. it returns a number.

The Left function (red) extracts the leftmost end of a string, up to a numeric position in the string. Which suddenly explains why the InStr function was there. You use it to extract all of the characters to the left of the dash. So now the red and orange parts are merely a string of a certain length extracted from a certain part of a presumably longer string.

The IsNumeric function (green) returns TRUE or FALSE base on whether a given string could be a digit string (with decimal point allowed). It is essentially asking "can I treat this string as a number string?"

The IIF function( blue ) tests for something being TRUE or FALSE (which explains the presence of IsNumeric). It has two more arguments. The second argument is returned if the first argument is TRUE. In the expression, that is the black argument. If the first argument is FALSE, the IIF returns the third argument - which is the string returned by the Left function.

Finally, you have the CDBL function (purple) which takes an input argument that is a number of some kind and converts that number to DOUBLE data type. What gets converted is either the entire string in [tblSU]![SUNum] or the left-most part of that string up to the first dash.

I hope this shows you the correct approach to deciphering complex expressions. It is basically a millennia-old technique that is still valid to this day, called "divide and conquer."

 

[stanhook]

@The_Doc_Man This is extremely kind and awesome of you to take the time to explain it in a way I can understand! I'll re-read it a few times so it all can sink in but this has been incredibly helpful!!

Thank you all for your great explanation and taking the time to help me understand what this is saying. THANK YOU!!!

Stan