Microsoft's KB Article says:
ACC: Trailing Spaces Automatically Truncated During Data Entry http://support.microsoft.com/kb/95009
Microsoft Access automatically truncates (or strips) trailing spaces on data that you enter in form controls to prevent the creation of a record that cannot be referenced because of accidental space character(s) being appended to the end of the text.
This behavior is by design.
1. Enter the following for the field's ValidationRule property: ="a "
2.Enter "a" (without the quotations marks and no space) in the field. Note that you do not receive a validation-rule-violation message.
The problem I have is that I have an unbound TextBox and I type a string of characters followed by a space... and then Access' TextBox removes the space as soon as I tab out of it... You can't switch this Behaviour (by Design) off...
Don't use Access databases is the most common answer... Altho I have seen others:
1. why would you need to do that? - which then goes on to describe how you can add strings: string1 & " " & string2 .. if you happen to want to concatenate strings... thanks .. but no...
2. use ALT 0160 to enter a character that looks like a space... hmm.. well then the search I am doing won't find the space I am trying to match... and I can't go through the database replacing spaces with 0160... and I am definitely NOT going to tell all my users .. oh just press ALT 0160 ... oh yes.. you have to use the number pad... oh yes, you have a laptop.. you have to press Function KEYPAD .. then ALT 0160 .. then Function KEYPAD to switch off the number pad... it took 4 answers to explain some of that in the following forum...
http://thedailyreviewer.com/office/view/preserve-trailing-space-entered-into-text-box-103342802
3. "I personally can't think of a reason to store trailing spaces." - that's my favourite... Solipsist argument #1 ... I can't think of a reason why you want to do that therefore you do not exist. :) And here's a solution to another problem... see if that helps you...
Well the answer is ... you can't switch this behaviour off... because for the most part Microsoft want you to have text fields with no trailing spaces... and no mistakes...
Elsewhere you would correctly trim all your entries where you need to... but in Access you don't need to because Mother Access ties you laces for you...
Here's the issue...
I have a database... (and for now it's going to stay as an Access database, because there's no money to pay me to write a .NET application to replace it... there's money to patch up...)
I have a Search Form. On that search form I have lots of TextBoxes. The user wants to search on "SE1 " with a space! They enter "SE1 " - they don't want to enter SE1~ALT-0160~... The tab to the next control... or press Search... and immediately Access has corrected this unbound control back to "SE1"
If they enter "SE1", then the search returns "SE1 2WR", "SE11 1JR", "SE12 2BN", "SE1 9XZ"
If they enter "SE1 ", the search returns "SE1 9XZ", "SE1 2WR"
This works so well in a C#/VB.NET GUI... Why has Access TextBoxes decided to do this?
My answer is a simple one... (and perhaps only solves my problem...)
Private Sub Postcode_KeyPress(KeyAscii As Integer)
If Chr(KeyAscii) = " " Then
KeyAscii = 45
End If
End Sub
This stops the user from entering spaces into a TextBox on an Access form. There are NO dashes - Chr(45) - in UK Postcodes.
Then in the search I must replace the spaces in the Postcode with dashes also... to check - to prevent Access from ignoring spaces in search strings...
If Left(Replace(rs("postcode"), " ", "-"), Len(Me.Postcode)) = Me.Postcode Then
' Add to results
End If
I've been testing this for an hour or so and it works well... good for finding Postcodes in the UK anyway...
It could be used to search on spaces in other data fields as well... so long as '-' is not used... but then a different character can be found to replace that...
No comments:
Post a Comment