SQL : How to add the number of characters of two records together?

EIREHotspur

I am developing a quiz.

I have answers in different records in an Access Database.
I have my Multimedia program reading the answers from the Access Database.

How do I get an imput box to show the total of characters added from 2 records.
Lets say answer A is Spain and Answer B is Italy.....the answer in the imput box should show 10.

I tried
select char_length(country) from db1
where country_id = "4"

I managed to add two records together in a variable which shoed SpainItaly in an imput box.
How can I count the characters in that imput box?

frezzabelle

I'm sure theres a Len function you can use. Or if you get back the two answers concatenated in your program why not just call .length() on them(well depending on the language).

blorg

Len(String) will give you the number of characters then you just need to add them together (+)

What you are currently doing is concatenating the strings, that is what the operator + does on strings. With numbers it will add them.

Len(StringA) + Len(StringB)

or indeed,

Len(StringA+StringB)

although the first is more intuitive to me.

EIREHotspur

Frezz....blorg....thanks for taking the trouble to reply.

I figured it out about an hour after posting here.

Like you both said Lens(string) was the answer.

Cheers lads.

EIREHotspur

Been trying to find how to do the following or even if it is possible.

How would I write a query to look at a record from the Access Database and do an action based on if a word had "s" as it's third letter AND "t" as it's fourth letter.
ie: Westmeath

Is it even possible?

Can anyone reccomend a good SQL website for looking these kinds of queries up?

blorg

Do a Google search for SQL functions, specifically Access.

SUBSTRING/SUBSTR is the function in MSSQL/Oracle but it is called MID in Access, same as in Visual Basic.

Something along the lines of:

WHERE MID(field,3,1) = 's'
AND MID(field,4,1) = 't'

will give you the matching records.

EIREHotspur

Thanks for the reply again blorg.

It isn't so much the records I am after as showing the result of a record with words clipped and shown in a field in my program.

Tor instance if I wanted to take dublin from the access database and clip the first 2 letters and the last 2 letters and have the result "li" in the imput box in my program.
That becomes my password and if the program sees "li" it allows the user to progress to page 2 and if not it throws up an error.
This is the best way I can impliment my passwords.

I found this page which shows how to do it with Substr but I have had no luck finding it to work with Access.

http://www.1keydata.com/sql/sql-substring.html

I will keep looking....thanks.

Edit: OK I figured it.

Very strange but the Multimedia Program I use has Substr and it works with Access....I thought it wouldn't and was trying MID.
The example on the page link I posted done the job perfectly....steep learning curve when your going around in the dark.

blorg

I have no idea what you are doing but am not sure you are implementing passwords in the best way. Do a search for passwords, database, and hashing.

EIREHotspur

I don't want to use hash passwords.

Heres the thing....multimedia program pulls data from database.
Program is exported as an executable but the access database sits alongside it open for manipulation by anyone.

I want the data so it cannot be changed by anyone and putting a password on the database is useless because the program could be copied and the Access database changed by those with the password.

This way I can booby trap the program to shut down if anyone changes the data in the Access database.
Thats why I have to do it this way.

thanks

Procasinator

If you are using this for security, you are going down the wrong route.

I could analyse your programme and see what you change, and not change it.

OR

I could change your programme (just because it's compiled, doesn't mean it's fixed).

It's kind of like websites that don't allow you to right-click in a bid to stop copyright infringement: you'll stop some, but it's easily by passable to someone who knows what they are doing.