Insert empty numeric value

Dear all,

          In VB, I have three textbox which are amount1,amount2 and
amount3. After user enter
the value in the textbox, I will insert the value into Access table.
The table have three columns
amount1 , amount2 and amount3, and all are nummeric Type. However, if
the user do not enter
any value in textbox . The insert statement will become as follows:

 Insert into table1 (amount1,amount2,amount3) values (,,)  Then access
complain that
there is syntax error in insert statement. Does that mean I cannot
insert empty value for the
numeric value in access.? How to solve this problem. Thanks.
0
hon123456
11/27/2007 7:46:14 AM
access 16762 articles. 3 followers. Follow

3 Replies
1274 Views

Similar Articles

[PageSpeed] 49

A simple solution would be…

if IsNull(amount1) then
Insert into table1 (amount2,amount3) values (,,)

If isnull(amount2) then
Insert into table1 (amount1,amount3) values (,,)

If isnull(amount3) then
Insert into table1 (amount1,amount2) values (,,)

hon123456 wrote:
>Dear all,
>In VB, I have three textbox which are amount1,amount2 and
>amount3. After user enter the value in the textbox, I will insert the value into Access table.
>The table have three columns amount1 , amount2 and amount3, and all are nummeric Type. >However, if the user do not enter any value in textbox . The insert statement will become as follows:
> Insert into table1 (amount1,amount2,amount3) values (,,)  Then access
>complain that there is syntax error in insert statement. Does that mean I cannot insert empty value >for the numeric value in access.? How to solve this problem. Thanks.

-- 
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200711/1

0
AccessVandal
11/27/2007 8:46:08 AM
Firstly the amount columns in the table must allow Nulls if you want them to 
be empty rather than contain zeros.  In Access this means the column's 
Required properties must be False.

You'd build the SQL statement to include NULL if no value is entered into a 
text box.  In VBA in Access you can use the Nz function, but I don't think VB 
supports this function.  You should be able to use something along the 
following lines, however:.

Dim strAmount1 As String
Dim strAmount2 As String
Dim strAmount3 As String
Dim strSQL As String

If IsNull(Amount1) Then
    strAmount1 = "NULL"
Else
    strAmount1 = Amount1
End If

If IsNull(Amount2) Then
    strAmount2 = "NULL"
Else
    strAmount2 = Amount2
End If

If IsNull(Amount3) Then
    strAmount3 = "NULL"
Else
    strAmount3 = Amount3
End If

strSQL = INSERT INTO Table1" & _
    "(amount1,amount2,amount3) " & _
    "VALUES(" & strAmount1 & _
    "," & strAmount2 & "," & _
    strAmount3 & ")"

''''code to execute strSQL statement goes here''''

Often its is preferable to avoid Nulls, however, particularly with numeric 
or currency data and use a default value of zero.  Nulls are not values but 
the absence of a value and are therefore semantically ambiguous.  Take the 
example of a CreditLimit column in a Customers table.  What would Null mean?  
Zero credit?  Unlimited credit?   There is no way of knowing from the data 
per se, it’s a matter of interpretation.  The nearest one can get to a 
meaning for Null is 'unknown'.

Ken Sheridan
Stafford, England

"hon123456" wrote:

> Dear all,
> 
>           In VB, I have three textbox which are amount1,amount2 and
> amount3. After user enter
> the value in the textbox, I will insert the value into Access table.
> The table have three columns
> amount1 , amount2 and amount3, and all are nummeric Type. However, if
> the user do not enter
> any value in textbox . The insert statement will become as follows:
> 
>  Insert into table1 (amount1,amount2,amount3) values (,,)  Then access
> complain that
> there is syntax error in insert statement. Does that mean I cannot
> insert empty value for the
> numeric value in access.? How to solve this problem. Thanks.
> 

0
Utf
11/27/2007 9:48:01 AM
Try

Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef
Dim i As long

Set Db = Access.CurrentDb
Set Qdef = Db.CreateQueryDef(VBA.vbNullString,VBA.vbNullString)
Qdef.SQL = "PARAMETERS pAmount1 Double, pAmount2 Double, pAmount3 Double;" & 
VBA.vbcrlf & _
   "INSERT INTO TABLE1 (Amount1, Amount2, Amount3)" & VBA.vbcrlf  & _
    "VALUES( pAmount1, pAmount2, pAmount3)"

For i = 1 To 3
  Qdef.Parameters("pAmount" & i).Value = Me.Controls("Amount" & i).Value
Next

Qdef.Execute DAO.dbSeechanges
Set Qdef = Nothing
Set Db = Nothing

HtH

Pieter


"AccessVandal via AccessMonster.com" <u18947@uwe> wrote in message 
news:7bd4c29bfaaf9@uwe...
>A simple solution would be.
>
> if IsNull(amount1) then
> Insert into table1 (amount2,amount3) values (,,)
>
> If isnull(amount2) then
> Insert into table1 (amount1,amount3) values (,,)
>
> If isnull(amount3) then
> Insert into table1 (amount1,amount2) values (,,)
>
> hon123456 wrote:
>>Dear all,
>>In VB, I have three textbox which are amount1,amount2 and
>>amount3. After user enter the value in the textbox, I will insert the 
>>value into Access table.
>>The table have three columns amount1 , amount2 and amount3, and all are 
>>nummeric Type. >However, if the user do not enter any value in textbox . 
>>The insert statement will become as follows:
>> Insert into table1 (amount1,amount2,amount3) values (,,)  Then access
>>complain that there is syntax error in insert statement. Does that mean I 
>>cannot insert empty value >for the numeric value in access.? How to solve 
>>this problem. Thanks.
>
> -- 
> Please Rate the posting if helps you
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/200711/1
> 


0
Pieter
11/27/2007 9:49:22 AM
Reply:

Similar Artilces:

Mail sent from empty Outbox !
OL2000: Very often I see message "sending from outbox" and something goes out even when outbox was empty. No trace of sent mail in Sent box. I use OL to read/send mail from my 3 different POP accounts, but this happens only with Compuserve acct. Use Norton Antivirus, Pest Patrol and Zone Alarm and none indicate any suspect activity. Any clue? On Sat, 1 Nov 2003 00:26:20 -0800, "Radi" <anonymous@discussions.microsoft.com> wrote: >OL2000: Very often I see message "sending from outbox" and >something goes out even when outbox was empty. No trace o...

Finding Next Empty Cell in a Range
My worksheet is set-up for 12 month price tracking. The entry range is 12 rows (months) by 31 columns (days). How can I write a macro that will locate the cursor to the next empty cell in the range? What I am looking for is an efficient "find" command. I would prefer to execute by ctl-(letter) if possible. Thanks for helping. Need more information - what last/empty cell are you looking for? Next empty one in a row or column? Do we need to consider what the current month or day is? While we're figuring that out, here's code with 3 separate 'sea...

Access Code Pushing Values
I have a customer database with [BillAddress] and [ShipAddress]. I am using a "yes/no" box titled[SameShipAddress?]. To automatically fill [ShipAddress] after checking the "yes/no" box I used this code in the after update event for the check box. If Me![SameShipAddress?] Then If IsNull(Me![BillAddress]) Then Else [ShipAddress] = [BillAddress] End If End If This works for the selected customer, but then pushes the entry [BillAddress] of the previous customer to [ShipAddress] of all of the following customers. Any thoughts? Sounds as...

enter value based on drop down list of another field?
I have an excel 2003 spreadsheet with 3 worksheets. In sheet 1, I created a drop down list in column A based on values in sheet 3, column A (item number). Thisd data comes from sheet 3, where I have column A (Item #'s), column B (Item desc)an Column C (price) In column B of sheet 1, I have Item Descriptions and in column C I have price. I want this to automatically fill in based on what I enter in the column A sheet 1 and it should get the corresponding values from sheet 3 column B and C. I can make the drop down list in sheet 1 column B and C, but some of the descriptions need ...

insert logo using AUTOCORRECT
Have been trying to insert my company's logo using AUTOCORRECT. It works like a charm in WORD. It doens't work in Excel. ex. when I type "lg" these characters should be replaced by my company's logo. Does anyone know how to do this in Excel? -- digicat ------------------------------------------------------------------------ digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14920 View this thread: http://www.excelforum.com/showthread.php?threadid=497273 ...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

DVD Insert
I have been asked by a friend to design an insert for a DVD he is making. The only templates I've been able to find are the ones that are actually for CD jewel cases. He is going to be using the DVD cases like you see in the stores when you buy a movie, which is much larger and the insert goes into the plastic sleeve. It's about 10-1/2" X 7-1/4". I checked the Microsoft site, but they had nada. Does anyone have a place where I could get a template for this project, or any idea of the best way to set this up? I've done CD inserts, but never a DVD before. Thanks....

Insert hyperlink greyed out
Can't see any other posts on the above any advice valued. Thanks {XP2, with Publisher 2002} On Sun, 23 Oct 2005 08:39:37 +0100, Gel wrote (in article <1130053177.481108.189290@o13g2000cwo.googlegroups.com>): > Can't see any other posts on the above any advice valued. > Thanks > > {XP2, with Publisher 2002} > Ah yes, "can't see' the classic euphemism for "I'm a lazy f**kwit, I can't be arsed to RTFH, or to STFW. Please wipe my arse for me, because I'm too lazy to find the bog roll..." Where are you attempting to insert th...

Comparing cell values then labeling them based on comparision
Hi. I'm trying to find a function or way to compare two cells and then label them according to if one cell is lesser, greater, or the same as the other. I have a large data sheet where I want to compare many pairs of cells and then have them labeled in this way. For example for each pair where the second cell value is larger than the first it would put greater, where the first is larger it would be lesser, etc. Can anybody help me with this? also feel free to email me at andrew@metiri.com. Thanks so much for your help. > Hi. I'm trying to find a function or way to compar...

Sum of values in a form
Folks, I have read numerous threads on this topic, and understand that in order for my Sum() to work I need t ensure all references are made back to the Record Source. I have endeavoured to do this, but am still getting #Error. I am trying to Sum the revenue each tenant provides in a given date range, to give a total revenue figure. If the date range is a whole month, then the expression just takes the value of Rent PCM, and not Rent PCM * No. of Days, which would give some unusual results. My Expression looks like this: =Sum(Nz(IIf([Short Let]=-1,[Rent pcm],IIf(Day([Forms]...

Combo box default value multiautocomplete
Hi All i have a subform with 10 item. The first one is a combo box that can choose the value from a list that is linked to a table with many record. This subform is related to the master form with link master field codice,cognome,nome link child field codice,cognome,nome that are the other items that are not visualize in the subform. These link give me the right connection to the master form. Normally when i choose the value from list (table) i place also all the other item in the subform using this code in after update precedure Me.ISEE.Value = Me.ISEE.Column(0) Me.F_Costo_con_M...

Add cell value with mouse click
I want to create a counter, so that every time I click in a certain cell it takes the existing number and adds "1" to it. Is there any way to do this? I'm compiling statistical information, and need to get cumulative counts in various categories as I sort through the data. Hi! Maybe you can use a spinner button for this. Right click any toolbar then select Forms. Click on the spinner button ( it looks like an up arrow on top of a down arrow). Place it near the cell you want increment. Once you have it placed and sized right click on it and select Format Control. Fill in ...

IIF Query for Numeric Values in Text Field
I’m attempting to flag interest rate spread errors and omissions in a file from a sales database using IIF statements I adapted from another database (I’m a novice at this). The field I’m querying is text format and contains both alpha and numeric values. When I test for a null value the IIF works fine, but when I test for a numeric value I get an #ERROR. Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread Is Null),1,0) Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0...

How to get task manager CPU and default memory column values
I need to return CPU and memory columns for each process in server from web service like task manager shows by default. I tried code below but s.TotalProcessorTime throws Win32exception with stack below. How to get CPU and real memory size from server using web service ? Andrus. static void Processes(StringBuilder sb) { List<Process> processes = new List<Process>(); foreach (Process process in Process.GetProcesses()) processes.Add(process); processes.Sort((a, b) => { ...

removing empty cells in a rows of data
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I have may rows of data and I want to remove the empty cells. eg <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A B C D E F G H <br> 1 X X X X <br> 2 Y Y Y Y <br> 3 Z Z Z Z <br><br>I want it to look like this without having to copy/cut and paste. <br> &nbsp;&nbsp;&nbsp;&nbsp;A B C D <br> 1 X X X X <br> 2 Y Y Y Y <br> 3 Z Z Z Z <br><br>Is there any automated way to achieve this? Thanks Allan ...

Linking Values in Worksheets
Is there a simple method of linking a column of values in one worksheet to another worksheet? I can do it manually, one at a time but is there a way of copying the link down the column? (The columns are identical in length) Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1369 Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1370 Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1371 etc, etc Many thanks david Hi David try ='C:\Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C1369 and copy down Frank DavidM wrote: > Is there a simple method of linki...

most often occurent value(s)
I would like to count and determine the most often (10) occurent values (strings) in a column. How can I do that? Thank you very much your help. Tamas, Use a helper column of formulas. For example, in cell B2: =IF(COUNTIF($A$1:A2,A2)=1,COUNTIF(A:A,A2),"") Copy down to match your column, then use Data / Filter.. Autofilter and pick "Top 10" from the dropdown at the top of column B. HTH, Bernie MS Excel MVP "Tamas Konczer" <tamas.konczer@gmail.com> wrote in message news:1173871962.218348.123590@p15g2000hsd.googlegroups.com... >I would like ...

Inserting text closing or begining salutations in Office 2007
hi. I am starting to get to grips with Office 2007. I like the program and it's changes, but I am struggling to find some of the items I used to use in the previous version. For instance, how do I add closing and opening salutations such as "yours sincerily" or "dear sir" etc that we used to be able to do in the version before this. I have looked, but can't sem to find it anywhere. I appreciate any help or pointers with this. Many thanks. ...

#VALUE due to wrong entry
This formula represents dates dd mm yyyy over 3 seperate cells and deducted from a similar formula to work out how many weeks at �s per week. =IF(DATE(K7,J7,I7)-DATE(G7,F7,E7)>=6,229,0 It is used as part of a spreadsheet to work out a monetry deposit for rental. However when `renters` complete this all is ok unless they dont complete as mm (07) but instead type the month as in `July` This then returns #VALUE in the destination cell . Is there a way to change the formula to allow for the `renters` not completing as requested (mm), but still return �s rather than #VALUE I hope I ha...

Scroll Bar maximum value
I'm designing an interactive chart using a scroll bar object from the Forms Toolbar. The data for my chart gets updated daily and I'd like to have the Maximum Value of the scroll bar reflect the maximum number of data points. Is there a way to do this? The Maximum Value entry does not seem to accept a cell value or formula. I appreciate any help you can offer to solve this problem. Regards, Hi, The Maximum property can not be linked to a cell but you could use a cell to store the value and update the scroller if that value changes. Private Sub Worksheet_Change(ByVal Target...

Insert query with two left outer joins gives "Record is Deleted" m
Hi, I have built an insert query to combine data from 3 tables into one table based on a key value (RA_ID) on a form. Two of the from tables may or may not have data associated with the main from table. So I have coded the select from statement using left outer joins. When I run the query with a row in the first child table but not in the second it works fine but when I run the query with a key value that exists in the 2nd child table but not in the first I get a message saying "record is deleted". Anybody have any ideas what is causing this? Here is the query......... ...

Excel 97: Exporting Cell values to text files.
I was wondering if it is possible to set up a macro where you can sort a set of information once, then export certain cell values to a text file, and then sort it again, and export a different set of cell values to that same text file. I know how to get the Macro going and adding a sort to it, but I'm just not sure 1. if its possible to send cell values to a text file and 2. how one would program that into the macro. Thanks. Here's a couple of links that may give you an idea: Chip Pearson's: http://www.cpearson.com/excel/imptext.htm Earl Kiosterud's: http://www.tushar-meht...

How to get cell value with the ID from a fomula?
How to get the value from cell of colume A and raw "C3+4", and input to B2? I mean the fomula inputted in cell B2. Thanks. I am not sure what you mean but maybe this will help http://www.mvps.org/dmcritchie/excel/formula.htm Regards, Peo Sjoblom "mqiao" wrote: > How to get the value from cell of colume A and raw "C3+4", and input to B2? I > mean the fomula inputted in cell B2. Thanks. ...

.eml attachements appear empty #2
I have one user who when they recieve a .eml file attachement opens it and it appears to be a blank message. All other users can open these attachements if they recieve them. I have checked all settings over many times with no success. This person is using Outlook 2003. ...

Hide column is empty, not including header (row 1)
I'm lost on this one, but I'm sure there is simple macro code to do this. I need this in a macro as I'm massaging data that beyond removing empty columns. Option Explicit Sub testme() Dim myCol As Range Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks For Each myCol In .UsedRange.Columns If Application.CountA(.Range(.Cells(2, myCol.Column), _ .Cells(.Rows.Count, myCol.Column))) = 0 Then 'hide it myCol.Hidden = True Else ...