Please help with SQL. (1 Viewer)

[David8]

I've read up a bit on SQL, but basically I am a total coding and SQL beginner. I understand how to write some complicated Excel formulae, and that's about as close as I get (not very close).

To practice I thought I'd try what I thought would be a simple easy step. To create a calculated yes/no field. This is only an exercise. Field1 is Name and has a list of names. Field2 will be the calculated yes/no field. If the data in Field1 matches the criteria I specify for Field2, Field2 will hopefully return a tick (or check) for Yes.

So I already have my names field (Field1) and I click on Add Field, "Calculated" and then "Yes/No". The Expression Builder comes up, and I type in:

WHERE [field1] like "James".

I also tried just

[field1] like "James"

Surely this should be either True or False, and since I'm creating a yes/no tick (check) box then TRUE should translate to a tick, while FALSE should translate to no tick? Well obviously not! It doesn't work!

I lack the Syntax to tell Access what to do if the condition WHERE etc or LIKE is met. I assume that with a Yes/No box, what to do if the condition is met is obviously to tick the box. But it seems I must spoon feed this to Access. I obviously have a major syntax lack of understanding. Examples I've googled involved the line "Select" but I don't want to Select as I am not building a query, just an expression.

Please explain, and if possible explain the different parts of an expression such as what is an OPERAND, and then explain your answer in those terms, so I can make sure I code using all the correct coding "parts of a sentence" I'm probably using nouns and adjectives without any verbs or something. I completely lack the language to engage with this, but I'm sure I can master the logic.

 

[HiTechCoach]

You need to forget everything you know about Excel when working with Access. Almost nothing applies

1) Field1 is Name - Not Name is a reserved work and should be avoid in naming any object.

also see: Access Naming Conventions

About SQL:
2) The Where clause of an SQL statement is for selecting records. It is not for doing calculations or calculated fields.

3) A calculated field in SQL looks like this:

in the SQL

..., IIF([Field1] = "yes", True,False) as Field1TF,  ...

in the Query Designer:

Field1TF: IIF([Field1] = "yes", True,False)

Note: In Access the IIF() is similar to IF() in Excel.

3) In SQL the Like is used with wildcards in the Where clause for selecting records

[field1] like "James" is the same as [field1] ="James"

For example. if you want to see if James exists anywhere in a field use:

[field1] like "*James*"

See if this helps: Overview of Access Queries

4) In VBA to see is a string exists in another string use the Instr() function

Instr([Field1],"James")

I have given you all the pieces to do what you want.

The best way to learn this stuff is by trying to figure it out.

Let's see if you can use these clues to write your query.

Please post back your solution or any other questions.

 

[David8]

Thank you for your help.

IIf([field1] Like "James",True,False)

....will deliver True/False, as I want, or Yes/No, depending on my format choice in design view. I have put LIKE in there because it is more flexible than =. It has the potential for me to add wildcards even if I'm not using them in this simple example.

But It WILL NOT give me a check box. This seems especially nuts because if I simply write an expression

True

...then I get a nice column of ticked checkboxes. What am I doing wrong? When an IIF statement ouputs True why do I get True instead of a Tick? Why is Check/Uncheck not a formatting option? I DEFINITELY want a checkbox visual output. If Access won't give me it then I think that's silly and I hate it on that point.

Oh and what is "Field1TF:"? Clearly TF stands for True/False. Are you attempting to define some intermediate result to be used in the middle of the expression?

Also, InStr will deliver True/False???? I can only find information that it will deliver a numerical value. I suppose you could easily combine that with some kind of function that works like NotNull (don't know if that is a function, or how you create the equivalent, only through lack of time right now), or sorry probably just GreaterThan 0 in this case, hope you read this edit not an email thread update.

 

[David8]

Oh, and just tried something really simple. Created a new calculated yes/no box field. Set the expression simply to:

True

Nice column of ticked checkboxes.

Now I change the Result Type in design view to Text. Nice column of "-1 " as expected.

Now I change the Result Type in design view back to Yes/No. I now have a nice column of "Yes", or I can have "True", or "On". But can I get a tick in a checkbox back? No way! GGGGGGGGGGGGGGRRRRRR.

I know I'm now talking about formatting, but formatting bugs (if this is one) can be very annoying.

Thank you very much for your help earlier.

 

[HiTechCoach]

Oh, and just tried something really simple. Created a new calculated yes/no box field. Set the expression simply to:

True

Nice column of ticked checkboxes.

Now I change the Result Type in design view to Text. Nice column of "-1 " as expected.

Now I change the Result Type in design view back to Yes/No. I now have a nice column of "Yes", or I can have "True", or "On". But can I get a tick in a checkbox back? No way! GGGGGGGGGGGGGGRRRRRR.

I know I'm now talking about formatting, but formatting bugs (if this is one) can be very annoying.

Thank you very much for your help earlier.

You place the check box on a for or report. You have completer control over this.

I am not sure what you are referring to.

Ahhh ... just about to hit submit reply when I just thought about something:

Are you referring to the results displayed when you vire a query?

If yes, then IMHO you are worrying about the format in the wrong place or level. I expect queries ( a view of table(s) ) to return the data with NO formatting applied. I do all the formatting in the control on forms' and reports. This way I can use the same query and display the data in different ways.

I think you are confusing the the result for a query, or even a table, to with an Excel spreadsheet. While it looks a lot like an Excel spreadsheet, it is definitely NOT. A table or query is the raw data without any formatting applied.

In Excel a work sheet hold the data already formatted for presentation. In a database the data is stored without formatting. The presentation (formatting) is done at the form or report or even exported Excel for formatting.


Hope this helps ...

 

[David8]

Thank you for your help.

IMHO I am NOT worrying about formatting in the wrong place or level. You can create a manual check box (directly in a table), enter data into it, and it stays showing as a check box. There you do have control over it, so why not for a calculated field? Pointless.

For my purpose, which is the auditing of an equipment inventory for a large organisation, the conventions of viewing and entering data via forms and reports are not convenient. For me the process of viewing, entering and ammending data is all mixed up together. I will go into a room, and review what is there (many items), check it is correct and move on or ammend the data as necessary. Quite often I find that equipment has been moved around, so I will then need to quickly find other items of equipment and change their location attributes.

This is no good in a form as you can't see more than a small amount of data at once (AFAIK). It is absolutely no good in a report as that is not "live sortable" (unlike a query) so I'd have to flick back and forth between the pages of the report, and couldn't ammend the data as I did so.

For my purposes, I actually find using the database in a fairly 'raw' form works quite well. I don't enter data directly into tables any more, a redesign makes that inconvenient, but data entry into some of the queries I can now create is actually very convenient.

(I can well understand that with many applications where the user is not fully trusted not to mess things up, they need to be tightly controlled in what they can enter, and that is best done by forms.)

I cannot see any really good reason not to provide display control for calculated fields. Edit: I suppose this is a minor point really, as in practice I have so far only run into this problem for a calculated check box, which is not a big deal, and was originally only an exercise anyway. I shall move on.

 

[HiTechCoach]

IIf([field1] Like "James",True,False)

..
Also, InStr will deliver True/False???? I can only find information that it will deliver a numerical value.

That is correct. If the value is greater that 0 then it found the string

To use it properly in the case you would use something this:

IIf(Instr([Field1],"James")>0,True,False)

I suppose you could easily combine that with some kind of function that works like NotNull (don't know if that is a function, or how you create the equivalent, only through lack of time right now),

NotNull is written this:

Not IsNull()

 

[HiTechCoach]

Thank you for your help.

IMHO I am NOT worrying about formatting in the wrong place or level. You can create a manual check box (directly in a table), enter data into it, and it stays showing as a check box.

What you are referring to is the default control type to yse when adding the control to a form or report.

For my purpose, which is the auditing of an equipment inventory for a large organisation, the conventions of viewing and entering data via forms and reports are not convenient.

IHMO, form as always more convenient and superior than queries.

I have never use a query for data enter or would allow anyone to do so. When I an called to help people clean up there bad data, the first thing I as is do they edit data directly in the table or query. They usually almost always say yes.

I have create many accounting systems, inventory control system, Asset management systems, etc. All with high volume data entry. They all only use forms for data entry.

There are a few ways to get a form to view data more like what you want. Look at eh form's property for Default View. Change this to continuous view (I use this a lot) or datasheet view.