Trouble mapping 7 Access percentages to SQL Server 2008 r2 using variables in VBA

[Phred]

Access front end, Sql Server backend, Mismatched data type when appending to SQL Server. These are all LinkedTables.



I am pulling 7 percentages .25% into dimmed variables in VBA. The variables are included in a SQL Insert Into statement. The SQL statement runs fine except for the percentages. SQL server 2008 r2 fields are:

This is the Field Type setting in SQL Server:



Dimmed Variables in VBA statement. I have tried many different types of variables, Number, Varient, etc. Nothing works.

Dim varFYTFP4 As Variant
Dim varSYTFP3 As Variant
Dim varSYTFP4 As Variant
Dim varTYTF As Variant
Dim varVPFP5 As Variant
Dim varCEYEAR As Variant

This is the dlookup function that grabs the 7 variables from the Access lookup table:

NumFYTFP3 = Nz(DLookup("FyrTriP3", "dbo_feeScheduleLookup")) 'Tried Number it didn't work.

varFYTFP4 = Nz(DLookup("FYRTriP4", "dbo_feeScheduleLookup"))
varSYTFP3 = Nz(DLookup("SyrTriP3", "dbo_feeScheduleLookup"))
varSYTFP4 = Nz(DLookup("SyrTriP4", "dbo_feeScheduleLookup"))
varTYTF = Nz(DLookup("TyrTriFee", "dbo_feeScheduleLookup"))
varVPFP5 = Nz(DLookup("VOPtabFee5", "dbo_feeScheduleLookup"))
varCEYEAR = Nz(DLookup("CEyear", "dbo_feeScheduleLookup"))

This is the SQL statement the variables are in the last line of the VALUES.

Fields and variables are highlighted in Bold and Text size.

 
 
            stSQL2 = "INSERT INTO DBO_PropertyYearDetail" & _
                "( PropertyID, County, TownshipNM, TownshipNO, kPIN, CycleID, CycleName, LevelofAssess, kAttyAssigned, FirstYRTriFeeP3, FirstYRTriFeeP4, SecondYrTriFeeP3, SecondYrTriFeeP4, ThirdYrTriFee, VOPTABFeeP5, CEYEAR) " _
                & "VALUES ('" & varX2 & "','" & stCnty & "','" & stSelTwn & "','" & _
                stTwnNo & "','" & stKPIN & "','" & stCycleID & "','" & stCycleNM & _
                "','" & stLOA & "','" & stKattAs & _
                "',' & NumFYTFP3 & ',' & varFYTFP4 & ',' & varSYTFP3 & ',' & varSYTFP4 & ',' & varTYTF & ',' & varVPFP5 & ', ' & varCEYEAR & ');"

           DoCmd.RunSQL stSQL2

You will see different Dim types and different variables in the SQL statement. That is from my various tries, please ignore.

I cannot find the correct data types in Access VBA that will successfully write to the Sql Table. So far every type has failed.

What variable in the Access VBA statement works with Decimal(2.2) in SQL Server?

Any suggestions?

Fred

[June7]

Variables should not be within quote marks. Replace the apostrophes with quote marks.

"'," & NumFYTFP3 & "," & varFYTFP4 & "," & varSYTFP3 & "," & varSYTFP4 & "," & varTYTF & "," & varVPFP5 & ", " & varCEYEAR & ");"

Should probably specify the alternate value in the Nz() function.

varFYTFP4 = Nz(DLookup("FYRTriP4", "dbo_feeScheduleLookup"), 0)

Is there only one record in the lookup table?

Also, number type fields should not have apostrophe delimiters. Is PropertyID a number type?

Why are you saving CycleID and CycleName?

[Phred]

I followed all of your recommendations and it cured a lot of problems. I have one problem left. I can't get the dates to append to the tables in the queries. There are so many places the problem could be. Here is what I have:

There are lots of date fields coming up. The two dates/fields I am having an immediate problem with are:

TaxYear

OpenFile

This is the SQL table with the TaxYear field created as "Date".



Here is a screen shot of the Add New Property record.



Here are the date field's property settings.



Here are the DIM statements for the dates:

Dim dtTaxYear As Date

Dim dtOpenFile As Date

dtOpenFile = Me.OpenFile

dtTaxYear = Me.TaxYear

, #" & dtTaxYear & "#,

And here is the SQL statement:

stsql1 = "INSERT INTO dbo_Property" & _
      "( County, kPIN, TownshipNO, TownshipNM, CycleID, CycleName, CountyClass, PropertyDesc, LevelofAssess, Type, TaxYear, OpenFile, KattyAssigned, TaxCode)" _
      & "VALUES ('" & stCnty & "','" & stKPIN & "','" & stTwnNo & "','" & stSelTwn _
      & "','" & stCycleID & "','" & stCycleNM & "','" & stSelClas & "','" & stDesc _
      & "','" & stTypeNM & "','" & stLOA & "',#" & dtTaxYear & "#, #" & dtOpenFile _
      & "#, '" & stKattAs & "', '" & stTaxCode & "');"

The SQL date field is the type that shows the date without the time. Used in other places, like from data entry on screens, the date writes fine to the underlying table.

Thanks so much for your time.

Fred

[June7]

Don't see anything wrong with syntax - # delimiter always works for me but I have never used SQL Server as backend.

[Phred]

Thanks for all your help. I will wait to see if there are any more thoughts.

Syntax for SQL Server VBA date format.

Fred

[rpeare]

if you are appending to a SQL table from Access you have to use ' markers just like it was text. at least that's always been my experience.

[Phred]

What are markers?

[June7]

rpeare means apostrophe (') delimiters - just as you have for the other parameters in the SQL statement.

[Phred]

like, '# & dtYear & #',

Or

like, #' & dtyear & '#, ??

Would the marker be considered a wrapper?

Thanks

[rpeare]

no, # is an invalid marker in SQL (as far as I know)

... ",' " & dtTaxYear & " ' , ' " & dtOpenFile & " ', " ...

just make sure to remove the spaces I have put in for visibility.

[Phred]

Thanks both. I will give this a try. It just looks like a text string, just dimmed as a Date. I will report back on my trial so others will know.

Fred

[Phred]

rpeare and june7, thanks that did it. I had to drop the hashtag # and dim the date as a string:

SQL Server 2008 R2
SQL Insert Into statement
SQL Date field selection standard "Date".

dim stTaxYear as string
dim stOpenFile as string

'" & stTaxCode & "'
'" & stOpenFile & "'

Thanks for all your help.