Search results

filling null values/empty strings with a value is exactly what the NZ() function was created for I use NZ() calls within my dynamic SQL code to fill in missing values so the SQL won't break. Here's a snipped from some dynamic SQL code ... strSQL = strSQL & ", " & Chr(34) &...

I do recall concurrently referencing ADO and DAO libraries and having no compiler or run-time errors in MS Access 2000. (We just upgraded from MS Office 2000 to MS Office 2010 about a year ago - the same time we upgraded from Windows XP.)

I don't understand why the naming conflict error is being thrown...

How would I dismabiguate? My code compiles just fine without a reference to the DAO 3.6 library, but adding a reference to that library alone generates a error. All of my recordset DIM statements look similar to this Dim rst1 As ADODB.Recordset My file I/O DIM looks like this Dim...

I concur with everyone in the preference for an SQL approach when actually reading/writing data. I use recordsets to define loops that often build SQL (DML) action queries. Troubleshooting is easier for me using this approach, and code is often more portable when the back end database engine...

Recently I ran into a problem where I used the "Microsoft Scriptlet Library" to create external files and write content to them as part of an HL7 messaging integration. The problem with this approach is that the default encoding used when writing strings to these files was UTF-8 (a two bit...

Perhaps the original issue is simply a matter of empty verses null... I use the NZ function to fill empty or null values with a value, then check for the new value Try If ( nz([Forms]![Proposal_1AABEmployee_MasteR_Tab]![Proposal_A-Master_Table subform].[Form]![Prop_Appraisal...

Not having seen the videos, I can tell you that I often make standard code modules with useful functions, such as exporting recordset contents to MS Excel, logging run-time errors, performing file I/O operations, making Windows API calls, etc and importing copies of these modules into new MS...

MarkK, Thanks for your help here. the code to build strSQL is several hundred lines deep and was excluded because I thought it would only add an unnecessary level of complexity to the problem description. Likewise, there are a few hundred lines of code intended to use the rst1 recordset...

I just commented out the code that adds date criteria to the SQL statement to test your theory. Here's the result of the test: I extracted the value of strSQL (without date criteria) immediately before rst1 is created using a debug.print statement in the immediate window I successfully...

Gasman provided great advice for learning how to syntax troubleshoot any SQL statement, including UPDATE statements. The only think I would add is that double quotes can cause problems. I prefer to use ASCII character codes when writing dynamic SQL as they are clearly understood as...

I have what I think should be a relatively simple task that is particularly difficult to implement. I'm using a VBA module to dynamically build a SQL statement and assign it to the strSQL variable. SELECT EIWS_PFC_SURVEY_STANDARDIZED.* , EIWS_PFC_SURVEY_PERSON.Event ...

Thanks for your help here. I was hoping there was a way to do this leveraging stock DLLs. This would be so much simplier if I could only use PERL....

I need to post simple strings (actually HL7 messages) to a remote server via a TCP post using a named IP address and designated port. My goal here is to do this natively, using VBA, rather than introduce 3rd party "helper" tools. Composing the string is simple enough, but I am looking for some...

Minty, Thanks for the suggestions. I've already run a compact/repair on the file, but could not figure out how to do a MS Access repair/restore shortcuts like you used to be able to do from the MS Access 2K help menu bar drop down list. (I thought of this in the event that one of the client...

Here's the basErrorLog module Option Compare Database Option Explicit Private Const gstrObject = "basErrorLog" Private strError As String Public Function fnCreate_t99_Error_Log() '////////////////////////////////////////////////////////////////// '// Function: fnLog_Error...

Here's the basWindowsAPI module Option Compare Database Option Explicit Private Const gstrObject = "basWindowsAPI" Private strError As String Private Declare Function FindExecutable Lib "shell32.dll" Alias _ "FindExecutableA" (ByVal lpFile As String, ByVallpDirectory As String, _ ByVal...

This module and the ones below provide code I wrote to open files based upon file extension. It's the same idea with BLOB files, you would just write the file name to one field in the BLOB table, and store the file in an OLEDB field. The function fnOpenFile would receive the recordset value...

There are plenty of articles about using MS ACCESS to store and retrieve BLOBS (actually OLEDB Objects). I've modeled BLOB-type code in MS Access after Leigh Purvis' work successfully. Leigh has posted many valuable tips in these forums http://www.databasedevelopment.co.uk/examples.htm I'd...

ENVIRONMENT I have a form created with MS Access 2K that is now being used from MS Access 2010. File is stored on a network share and opened on client computers. BEHAVIOR One of the text box controls on one form just started showing 'name?' as the displayed value instead of a street address...