Re: textbox read in as a string?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Comments inline.

--

Ken Snell
<MS ACCESS MVP>



"James R." <James_DBA@xxxxxxxxxxxxxxxx> wrote in message
news:24A13C3B-2C8C-4919-AFE3-624C3B4A7908@xxxxxxxxxxxxxxxx
Hi,

Not sure if this is the correct newsgroup, I apologize if it is not.

I have an access database form that is used as a Front End application, it
is connected to a backend database of SQL Server 2005.

My problem is that I'm getting a "Type Mismatch" error; which indicates
that
the data being read into the VBA code is either being read in as a string
when it should be an integer, or vice versa.

Now, the problem part is that the data is coming from a textbox on a form
the textbox is called "Mult".

The code is:

Dim RushStatus as Integer
RushStatus = DLookup("TAT", "WorkOrders", "WorkOrder = '" &
Forms!Samples!Child1.Form!WorkOrder & "'")
If RushStatus = 0 Then Me!Mult = 2.5
Me!Mult.DefaultValue = Me!Mult

Ok, so the RushStatus reads in just fine as an integer value.

Do be careful. The Integer data type in ACCESS will not hold as large a
number as the Int data type in SQL Server.


If I debug,
the Me!Mult will be shown as NULL.

In the above code, Me!Mult is not set to a value unless RushStatus is a zero
value. So a NULL value for Me!Mult is very possible and probable.



What makes this even more puzzling is that the "Mult" column in the SQL
Backend isn't NULL!!! I've checked every single record and all of them
have a
value of 1 or higher.

Where in your code are you using the value from the Mult field in the SQL
Server table?



Now, to throw in a little more confusing item. Is the original backend was
an Access backend. If I run this same code on that (the Access version)
backend then Me!Mult gets read in as an Integer value (not as NULL).

Again, I ask -- where in your code are you reading / using the Mult field at
all?



I'm at a loss as to how to further troubleshoot this. I'd appreciate any
ideas someone would kick around for this.

I've used SQL Profiler and watched the TSQL coming in and it is just fine,
I've ran the TSQL through Management Studio and got back the proper
results.

For some reason, this textbox just won't see that there is a value in this
column.

Thanks in advance for any suggestions you might have!
James
--
Knowledge is the first step towards success. Little knowledge creates big
ideas.


.

Relevant Pages

  • Re: textbox read in as a string?
    ... and SQL; ... knowing what the maximum value of Mult ... and it works just fine if the backend is in Access; ... is being read in as NULL it is being processed as an Integer data type ...
    (microsoft.public.access.modulesdaovba)
  • textbox read in as a string?
    ... I have an access database form that is used as a Front End application, ... the problem part is that the data is coming from a textbox on a form ... What makes this even more puzzling is that the "Mult" column in the SQL ... Backend isn't NULL!!! ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • RE: RefreshLink error - 3078
    ... ACCESS backend, then have the backend connect to the SQL server. ... the front-end. ... linking wizard to link my DSN. ...
    (microsoft.public.access.conversion)
  • Re: Type Mismatch Error Message
    ... >> Using Text as SQL data type. ... I created a database on a SQL server manually and matched the column names ... I thought you "didn't use the database wizard?" ... >> Data type in form is No Constraints. ...
    (microsoft.public.frontpage.programming)