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

[kcdoell]

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

[ADezii]

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:

Dim strSQL As String
 
If IsNull(Me![Category]) Or IsNull(Me![CatType]) Then Exit Sub
 
DoCmd.SetWarnings False
  strSQL = "INSERT INTO TypeEorD(EorDName, EorD) "
  strSQL = strSQL & "Values('" & Left$(Trim(Me![CatType]), 1) & "_" & Trim(Me![Category])
  strSQL = strSQL & "','" & Trim(Me![CatType]) & "')"
    DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

[kcdoell]

Your solution worked flawlessly. Thanks a million.

Keith.

[ADezii]

Your solution worked flawlessly. Thanks a million.

Keith.

You are quite welcome.