Modules & VBA :: Replacing Empty String With Null Value
Sep 12, 2014
I have a module that creates records into a contacts table in my access database. When there is no data in the field from outlook, the data is populated as an empty string "" instead of a null value, creating problems with subsequent queries and processes. I'd like to create the data as a null value but the module keeps failing with "object is required" on the statement
It doesn't seem to like System.DBNull.Value or DBNull.Value, and when I assign the vbNullString constant instead, it gets set to "" again and not null.
I am having a query, where a few date records are null because of no entry or record, what a want that the field with no date entry to be come up with zero "0" .
In a previous post someone mentioned a difference between Null and Empty. Is Null is not pulling the nulls but I know those fields are empty. Can someone tell me how to pull in these empty fields. for my calculation I need to get Companies that showed revenue after and didn't show revenue once their contract started.
I have the query below and it returns the number of cases for eache of the case status (open, closed or private) Some of the cases have no status, the field is empty. Is there a way to count the number of cases which have no status in the status field?
Can someone tell me how to do this?
SELECT Count([Report table part one].CaseStatus) AS CASES, [Report table part one].CaseStatus AS STATUS
I have a form, form1. Inside there is a combo box, cbo1, that gets its values from table1.On form load, I want an expression that checks if table1 has any values to "send" to cbo1. If yes, meaning, if after form loads cbo1 is loaded with table1's values, I want cbo1 colored RED, if not, colored WHITE.
Im trying to concatenate a bunch of fields (50 arghh) which each are either blank or just contain one letter. This was someone elses setup for an attendance register which I think is an odd way of doing it. I would have used one field and then to get the mark for a week take a substring at the appropriate position. Anyway im trying to replace an empty string field with a letter to represent the register hasn't been marked using sql statement:
SELECT [400 Student Marks].acad_period, [400 Student Marks].student_id, [400 Student Marks].register_id, [400 Student Marks].register_group, Replace([1],"","U") AS attendance FROM [400 Student Marks];
[1] being the first register week then I would have concatenated with [2] etc...
This however causes an error each time on the attendance field so im guessing replace doesn't work on empty strings. Is there a way round this/alternative.
I have an Access 2010 database where we have a SQL Linked Table with a column that is nVARCHAR(20) Not Null data type. We have created a form for data entry. Currently when the user tries to erase a value or choose not to define a value we get the following error.
"You tried to assign the Null value to a variable that is not a Variant data type."
This field should accept a blank value "" as the user may not want to set the value. We do not have control over the DB schema, so how can I work around this issue in access?
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code: Private Sub cmdUpdateDates_Click() '################################### 'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time]. '################################### Dim intCounter As Integer intCounter = 0 Dim rs As ADODB.Recordset
I have a module for importing several excel files from a folder in Access. The user is prompted to select the folder which contains the Excel files.The issue:The code works fine if I select a folder from my company network drive (strFile variable returns with correct file name).The code does not work if I copy and paste the same folder to my desktop and select this location (strFile variable returns empty)
Code:
Option Compare Database Private Sub btn_ImportData_Click() Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim intWorkbookCounter As Integer Dim lngCount As Long Dim objExcel As Object, objWorkbook As Object Dim colWorksheets As Collection
I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I split it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).
Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a long time and that's because it has a lot of empty values in that array. How to get rid of those empty values/not store them in the first place?
Code:
Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")
While rop.EOF = False ReDim Preserve ArrRepOrder(j) ReDim Preserve ArrItem(j)
How do i use DMax() function instead of Autonumber, since the table is empty and it wont know what to do with Dmax() + 1 for next record?
E.g I have a new table Customer_details which contains
customerID customerName Age
now if i enter data through forms, i want the CustomerID to be incremental for each new record, so i can use Dmax() function. But since the table is empty how can i tell the form, through VBA that if it is null store 1 else store Dmax("CustomerID", "Customer_Details") + 1
The overall goal is to hide a whole line of a report using "canshrink" on a series of boxes, some of which are numbers and some that aren't. Some will be blank when the data is loaded into the form and I want these to make the rest of the form adjust.
Code: Private Sub Form_Load() If Me.Label33.Value = "<1" Then Me.Label34.Caption = "Nothing." End If End Sub
Our quotation tool, which is built in access 2003, has a memo field, which is extracted in a .txt file, to be uploaded by another system.
When the user starts the memo field with a blank line, by hitting the enter key, the upload file writes the memo field as a new line, separating it from the line indicator and thus giving errors.
My question would be : How would I determine in VBA, whether the first line of the memo field is blank.
I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.
So, I created a dummy database for the sake of learning the code behind this process. I've got 2 tables (but no microphone.
1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days)) 2) tbl_Register (where I want forecasted transactions to wind up)
I've got 2 saved queries:
1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register' SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description GROUP BY tbl_InitialPoint.Description;
2) qry_InsertTransactions (populates 'tbl_Register') INSERT INTO tbl_Register ( PostDate, Description, Amount ) SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];
And I've got a form called 'HomePage' that has
1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop 2) A button to start the process
It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).
I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with.
Public Sub InsertTransactionsBttn_Click() Dim db As Database Dim qdfNew As QueryDef Dim qdfInsertTransactions As QueryDef Set db = CurrentDb()
