473,322 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to format a string value in VBA?

Hi All,

Probably a simple question with a simple answer that I am missing.

I am using VBA to update a table with 9999 incremental values to act as a reference number preceded with a user defined double letter combination ie. SA1234

This is working fine, the problem is for values such as 0001, 0002 the value is being entered as 1,2 and drops the 0's.

Is there a way to keep the 0's in place?


Many thanks in advance.

Carl
Jun 21 '10 #1
7 7090
patjones
931 Expert 512MB
In my work I also must make extensive use of "numeric" values preceded by zeroes, and the way this is done is to make the column's type text, and give it the format "0000". This should prevent the leading zeroes from getting dropped.

Pat
Jun 21 '10 #2
@zepphead80
I have tried this, if I set the format and manually enter the data it works ok.

As soon as I try using the AddNew VBA function it seems to bypass the format and I have no zeros again.

I also think I need to be working with Strings so that I can add one value to the other ie. Alpha Characters selected by the user as string 1 and numeric values 0001 through 9999 as string 2. Which gives the end result of AA0001 etc.

Any further help would be appreciated! :)
Jun 21 '10 #3
patjones
931 Expert 512MB
Can you post the code that you are using to put the values in the table?

You are correct in using strings. You want to be able to insert the concatenated string, as in str1 & str2.

Pat
Jun 21 '10 #4
@zepphead80
Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Click()
  2. On Error GoTo Err_Save_Click
  3. Dim ref As String
  4. Dim DepartmentName As String
  5. Dim DD As String
  6.  
  7. DD = "DD"
  8.  
  9. ref = Me.Reference
  10. DepartmentName = DLookup("[name]", "customers", "[customerregistrationnumber]='" & Me.Customer_ID & "'")
  11.  
  12. Set NIS_Database_V2 = CurrentDb
  13. Set Appointments = NIS_Database_V2.OpenRecordset("Appointments")
  14.  
  15. Appointments.AddNew
  16. Appointments("referencenumber").Value = DD + ref
  17. Appointments("Date").Value = Date
  18. Appointments("starttime").Value = Now
  19. Appointments("Durationhours").Value = "0"
  20. Appointments("realhours").Value = "00:00"
  21. Appointments("Durationminutes").Value = "0"
  22. Appointments("realmins").Value = "00:00"
  23. Appointments("endtime").Value = Now
  24. Appointments("Interpreter").Value = "The Big Word"
  25. Appointments("language").Value = Me.Language
  26. Appointments("departmentID").Value = Me.Customer_ID
  27. Appointments("DepartmentName").Value = Me.Name1
  28. Appointments("inputby").Value = Me.Taken_By
  29. Appointments("inputon").Value = Date
  30.  
  31. Appointments.Update
  32.  
Sorry for the delay in getting back to you with this...
Jun 27 '10 #5
patjones
931 Expert 512MB
The combination of two or more strings is accomplished using the concatenation operator "&", so in your case you would write this:

Expand|Select|Wrap|Line Numbers
  1. Appointments("referencenumber").Value = DD & ref

The "+" may or may not give the correct results, as it is usually used for arithmetic. As a seemingly unrelated side note, I see that you are not using DepartmentName anywhere in the code below the DLookup call. Just wondering what's going on with that...

Pat
Jun 27 '10 #6
@zepphead80
Tried that and it still doesn't seem to have done the job...

The primary table is updating on save with the reference number being stored perfectly.

The second table which is updated via the AddNew still stores the string as DD15 instead of DD0015!

As for the DepartmentName bit, I was having problems recording the DepartmentName correctly so tried that and have omitted to remove the code since playing with it! Nice spot - thanks :)
Jun 27 '10 #7
ADezii
8,834 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. Dim ref As String
  2. Dim DepartmentName As String
  3. Dim DD As String
  4.  
  5. DD = "DD"
  6.  
  7. ref = Format$(Me.Reference, "0000")
  8. DepartmentName = DLookup("[name]", "customers", "[customerregistrationnumber]='" & Me.Customer_ID & "'")
  9.  
  10. Set NIS_Database_V2 = CurrentDb
  11. Set Appointments = NIS_Database_V2.OpenRecordset("Appointments")
  12.  
  13. With Appointments
  14.   .AddNew
  15.     ![referencenumber] = DD & ref
  16.     ![Date] = Date
  17.     ![starttime] = Now
  18.     ![Durationhours] = "0"
  19.     ![realhours] = "00:00"
  20.     ![Durationminutes] = "0"
  21.     ![realmins] = "00:00"
  22.     ![endtime] = Now
  23.     ![Interpreter] = "The Big Word"
  24.     ![Language] = Me.Language
  25.     ![departmentID] = Me.Customer_ID
  26.     ![DepartmentName] = Me.Name1
  27.     ![inputby] = Me.Taken_By
  28.     ![inputon] = Date
  29.   .Update
  30. End With
Jun 27 '10 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: faktujaa | last post by:
Hi, I have date in a string format like string strDate = "110204". Now i want to convert this string to MM/dd/yyyy format. How do i do this???? I used string.Format(strDate, "MM/dd/yyyy") but no...
1
by: Ed S | last post by:
I'm trying to format a double with this behavior: 1) should only show 8 characters (character count before and after decimal added together). 2) if greater than 7 characters to the left of the...
2
by: MM | last post by:
Hi, I have a param class containg these vars:- string key; // eg: "WN" object value; // eg: 1.2 string format; // eg "F2" and I output these to a StreamWriter using ...
15
by: JenHu | last post by:
Hi expert, I have a data field (birthdate) contains a datetime datatype (mm/dd/yyyy), how can I convert to mmdd?? say, the birthday is 8/1/1979 and convert to mmdd DOB =...
2
by: mdeaver2003 | last post by:
I'm trying to output a double using a precision that varies, governed by the value of a precision variable. In C I can do it like this: double pi = 3.14159; int prec = 4; printf( "%.*f",...
28
by: Andre | last post by:
Hi, Does anyone know whether the ECMA, or an other standard document, specifies a maximum for the value that can be pass to the setTimeOut() function in Javascript? Andre
8
by: Rick | last post by:
VB.Net 2005 I have a non-bound datagridview that I fill with a column that contains a UPC number (as a string). I want the display format to be like this: 0 12345 67890 1. I have tried...
7
by: Tony Girgenti | last post by:
Hello. Trying to develop VS2005, SP1 windows form VB program. When i try a statement like below and the data is "24.95", without the quotes of course, regPriceString equals "000000000025+" . ...
7
by: Andrus | last post by:
How to create format string for decimal data type which shows blank for zero and default format otherwize ? I tried format string "f;f;#" but this shows f for nonzero numbers. Andrus. ...
3
crystal2005
by: crystal2005 | last post by:
Hi all, As the title, how do i retrieve a value from the URL format string after i pass it to the other page. E.g. example in my case is Say in admin.aspx i have a link to questions.aspx. I...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.