By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Adding a Character to a string, using INSERT INTO....

kcdoell
100+
P: 230
Hello:

I have a Insert Into command that currently works:

DoCmd.RunSQL "INSERT INTO TypeEorD ( EorDName, EorD ) SELECT Trim([CatType] & '_' & Trim(Category)), Trim(CatType);"

"Category" is a manual input text box
"CatType" is a combo box where the user selects either "Deduction" or "Earning"

If the user inputted in the Category text box "Cola" and Selected Earning from the CatType, the current code would insert into my table (TypeEorD) the following result:

EorDName =Earning_Cola
EorD=Earning

What I am trying to do is get the following result:

EorDName =E_Cola
EorD=Earning

I thought changing the code to the following would produce the result but it does not:

DoCmd.RunSQL "INSERT INTO TypeEorD ( EorDName, EorD ) SELECT Trim(Left([CatType],1) & '_' & Trim(Category)), Trim(CatType);"

The result I get is this:
EorDName =_Cola
EorD=Earning

Does anybody have any insight?

Thanks
Jan 4 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,623
Hello:

I have a Insert Into command that currently works:

DoCmd.RunSQL "INSERT INTO TypeEorD ( EorDName, EorD ) SELECT Trim([CatType] & '_' & Trim(Category)), Trim(CatType);"

"Category" is a manual input text box
"CatType" is a combo box where the user selects either "Deduction" or "Earning"

If the user inputted in the Category text box "Cola" and Selected Earning from the CatType, the current code would insert into my table (TypeEorD) the following result:

EorDName =Earning_Cola
EorD=Earning

What I am trying to do is get the following result:

EorDName =E_Cola
EorD=Earning

I thought changing the code to the following would produce the result but it does not:

DoCmd.RunSQL "INSERT INTO TypeEorD ( EorDName, EorD ) SELECT Trim(Left([CatType],1) & '_' & Trim(Category)), Trim(CatType);"

The result I get is this:
EorDName =_Cola
EorD=Earning

Does anybody have any insight?

Thanks
This code has been tested and will achieve the desired result:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. If IsNull(Me![Category]) Or IsNull(Me![CatType]) Then Exit Sub
  4.  
  5. DoCmd.SetWarnings False
  6.   strSQL = "INSERT INTO TypeEorD(EorDName, EorD) "
  7.   strSQL = strSQL & "Values('" & Left$(Trim(Me![CatType]), 1) & "_" & Trim(Me![Category])
  8.   strSQL = strSQL & "','" & Trim(Me![CatType]) & "')"
  9.     DoCmd.RunSQL strSQL
  10. DoCmd.SetWarnings True
Jan 4 '08 #2

kcdoell
100+
P: 230
Your solution worked flawlessly. Thanks a million.

Keith.
Jan 4 '08 #3

ADezii
Expert 5K+
P: 8,623
Your solution worked flawlessly. Thanks a million.

Keith.
You are quite welcome.
Jan 4 '08 #4

Post your reply

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