convert a Nz from MS Access to a zero-length string.

convert a Nz from MS Access to a zero-length string.

Hello, I am migrating the Access queries in SQL server views.
This is a query in Access:
SELECT emp_name, nz(emp_address)
FRM employee

Do you know how to convert this in SQL server VIEW? especially "nz"....
Thanks.
Jenny.


Post your answer or comment

comments powered by Disqus
We have WSS3. I am a site collection admin. The SharePoint server is at remote location at our vendor.
I can link from MS Access to all the cusom lists - lists created by users.
I can not link from MS Access to the admin lists, for example the Site Hierarchy list. This is https://mysite.com/_layouts/vsubwebs.aspx
I know this is a LIST. It has columns Site URL, Title, etc.
I need Read only access. How can I do it?

Hi,
I am currently migrating an database application from MS-Access to MSSQL Server 2005.

Details of MS-Access application
Certain VB Forms has been designed within the MS-Access database. They wil be talking to some tables in the MS-Access DB and make necessary updates.

What is that I have done so far?
I have now migrated all the data present inside MS-Access(Tables + data) to MSSQL 2005 Server. Created a VB6 application and designed the similar forums. I have made the Database connection using DataEnvironment variable and checked the connectivity to MSSQL 2005 Server DB. The Connection is fine. I am able to connect from VB6 to MSSQL 2005 Server. I copy pasted the entire piece of code from each form in MS-Access to the form in MSSQL 2005 Server. I guess this is NOT THE RIGHT WAY to do it. Not sure I tried to EXECUTE the VB6 application and getting the error in the line in the 1st form that would load Private Sub Form_Load()
Application.CommandBars("Menu Bar").Enabled = False ------ In MS-Access when I type in Application. the set of Options which can be used are displayed. But when I type Application. in VB6 nothing happens. VB6 is NOT ABLE to RECOGNISE this Application.

I want the EQUIVALENT of Application. in VB6

Since I haven't worked on VB before, I am not very sure how to proceed further. Can somebody HELP ME here?
WHAT TO DO NEXT ?

Regards
Kamath

I’ve read a lot of different article on the internet about being able to export a table from MS Access to Ms Excel.

Problem I’m having is that I don’t know which one would work best for me, so I thought since I’ve gotten such great advice from this form I will ask.

I have a query that updates a table in access.
What I would like to have happen in the export from access to excel

Currently I have a number of queries that updates number of table daily.
Then I manual export the tables to a excel files
Then I spend 15 -20 minutes formatting the excel fine. “I format all the table to excel files that same way.
Once this is done I email it out in excel format to different managers that need to make commits and then email the spread sheet back to me then I import the spread sheets and record there comments.

If at all possible I would like all the files to import into one spread sheet just into different tabs. I have a bout 4 different group.

Below are some of the articles I’ve read about exporting from access to excel.

Looking for any advice example ect

http://www.databasejournal.com/features/msaccess/article.php/3563671/Export-Data-To-Excel.htm

http://www.vb123.com/toolshed/98docs/excelbe.htm

http://www.tek-tips.com/viewthread.cfm?qid=1201057

http://zmey.1977.ru/Access_To_Excel.htm

http://www.databaselessons.com/access-data-to-excel-2.php


Thanks for all your help and advice
TCB

My database is setup to not except nulls. So, on my forms, if I leave a field blank, I get an error about the Null entry. If I enter a "" in the field and tab out of it, it appears blank, but is a zero-length string entry. How can I automatically take all Null form field entries and convert them to zero-length string values before the save happens so that I don't get this error? Thanks.

Hello, I'm trying to convert my contact info database from MS Access to ACT! version 6. So far I have been unable to find a clearcut converter so I suppose I will attempt to setup a macro somehow that converts them. Although unfortunately I dont know much about how to program a macro either. If you have any idea how I might be able to do either of these without having to go through each contact and paste the info into ACT! please email me at gtg225s@mail.gatech.edu I would greatly appreciate it!

Thanks, Pat

I can't find a way to link from MS Access to MS Project 2000 does anybody know how? Plz help.

Hi,

I have a question.

Can MS Access table link to Outlook?

Suppose there are Tom and Sue persons:

Sue manage the acc.mdb database file, The database has one table which is called "input", Sue made the form, the form is come from "input" table info. and also Sue made one button to send the specific record to the Tom via outlook, once Sue send out, the mail has created a link, which link can open the document (NOT attached file). Once Tom receive the mail from Sue, Tom will click the link to open the document, and also fill out one signature inside the document, after filled it out, then the signature will automatically populate in the specific field of the "input" table in the acc.mdb database file.

Can MS Access and Outlook do above situation?

Please let me know, thanks a lot.

Hi Guys,
I have a Ms Access database with a combo box called Locations. I want the Access Application to work in the following way:

When the user selects a Location from Ms Access (the Location Combobox) and clicks "Preview", Google Earth opens and zooms straight to the Location selected from Ms Access.

For instance, if "Texas" is selected from the Ms Access Combobox, Google Earth opens and zooms straight to "Texas" on the globe.

Please i need help in doing this.
It's really urgent!!!

I would like to export text data from MS Access using VB code. The export file needs to go to the public desktop directory in Windows 7. There are some permission issues when I do this. Has anyone been able to work around this?

Thanks,
Greg

--------------------------------------------------------------------------------

I had a project that was using SQL, and then had to modify it to put a date into a field, going from MS Access to Oracle. Here is what the code looked like:

strSQL = ""
strSQL = strSQL & "INSERT INTO MasterPN "
strSQL = strSQL & "(PN, Description, FSCM, "
strSQL = strSQL & "ATAPN, ESDS, SPN, "
strSQL = strSQL & "Keyword, CompVal, DimData, ModUser, ModDate) "
strSQL = strSQL & "VALUES ('" & rsTemp!PN & "', '" & strDescription & "', '" & rsTemp!Fscm & "', "
strSQL = strSQL & "'" & strATAPN & "', " & intESDS & ", '" & strSPN & "', "
strSQL = strSQL & "'" & rsTemp!Keyword & "', "
strSQL = strSQL & "'" & rsTemp!CompVal & "', '" & rsTemp!DimData & "', "
strSQL = strSQL & "'SYSTEM'" & Now()
conOracleDb.Execute strSQL, , adExecuteNoRecords

This did not place a date into the desired field. So, I deleted that piece of code and put in the following code:

strSQL = ""
strSQL = "SELECT * From MasterPN"
rsPart.Open strSQL, conOracleDb, adOpenStatic, adLockOptimistic, adCmdText
If Not (rsTemp.EOF = True And rsTemp.BOF = True) Then
rsPart.MoveLast
rsPart.AddNew
rsPart!PN = rsTemp!PN
rsPart!Description = rsTemp!Description
rsPart!Fscm = rsTemp!Fscm
rsPart!ATAPN = rsTemp!ATAPN
rsPart!ESDS = rsTemp!ESDS
rsPart!SPN = rsTemp!SPN
rsPart!Keyword = rsTemp!Keyword
rsPart!CompVal = rsTemp!CompVal
rsPart!DimData = rsTemp!DimData
rsPart!ModUser = "SYSTEM"
rsPart!ModDate = Now()
rsPart.Update
End If

And that code worked!! My question...... can anyone tell me why one way doesn't work but another way using the same Now() function does work? This one drove me nuts for the better part of a day.

Are there issues between MS Access date functions and date items in Oracle?

Any help and advice on this will be greatly appreciated. Thanks!!

Is there any tool available for converting reports from ms-access to java? if it is/not how can I proceed in it.

Hi,

I have a student database in MS-Access, And I want to fetch some part of data from ms-access to excel.There is a macro in access where 20 to 25 queries in that macro, I want to hit macro first so macro will do all the calculation in ms-access, then at end final query will store result data in "Student_Final_Result" table, After that I want to fetch all data from "Student_Final_Result" to excel in Sheet("Student").range("a1"), But I am not sure how will I do this.....

Database Name Student_Data.mdb
Macro_Name Stu_Calc
Temprary_Table Student_Final_Result
Excel_Workbook Student Result
Result_Sheet Student
Paste_Range Sheet("Student").range("a1")

Please help me to resolve this problem.

Thanks & Regards,
Kashif.

Hi !

I'm a newbie in ms access.
I have an access database that is getting larger and I have limited time to restructure the database and put it in sql server.
I need to buy time to increase the size of my access database.
What I'm thinking is that I want to migrate my access database to sql express.
Then, I want to be able to access my table in sql express from my microsoft access form.
Is there some kind of tutorial or procedure that need to be done to read data from sql express from access form?
Is there some kind of tutorial to migrate data from ms access to sql express in a fast manner?
Thanks

I am trying to find out how I can make a report by transferring the data from an Access Table to MS Excel spreadsheet showing dollar values for each customer.

so i have this string:


	Code:
	               
                          strSQL = "INSERT INTO tbl_school " & _
                                   "(school_name, school_degree, school_major, school_startdate, school_enddate) SELECT
(school_name, school_degree, " _
                                 & "school_major, school_startdate, school_enddate) FROM tmptbl_school;"
                          .Execute strSQL, , adCmdText + adExecuteNoRecords
                          strSQL = "SELECT Last_Insert_ID();"
                            With .Execute(strSQL, , adCmdText)
                                If Not (.BOF And .EOF) Then
                                    ' Found the new ID - build the second Insert SQL string
                                    lngLastSchoolID = .Fields(0)
                                Else
                                    ' Abort
                                End If
                            .Close
                            End With

the thing is tmptbl_school is wihtin the ms access front end. so it keeps telling me that the table does not exist which makes a lot of sense, does anyone know how to correct my syntax?

I want to run a Command Prompt instruction. Is it possible to do it pass it from MS Access, not from the Command Prompt Window?

Thanks,

I want to call a SQL server User Defined Function UDF from MS Access code and get its returned value.

I successded in calling it but I dont know how to get its returned value. The code I used is as follow:

MyIx = 50034
MySt = "20/12/2007 20:30:00"

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administ rator;DATABASE=Bahrain;Trusted_Connection=Yes")

SQL = "FindPSF" & " " & MyIx & "," & "'" & Format(MySt, "dd-mmm-yyyy HH:MM:SS") & "'"
db.Execute SQL, dbSQLPassThrough

The function FindPSF I want to call is as follow:

CREATE FUNCTION dbo.FindPSF (@Ix int,@St datetime)
RETURNS datetime
AS
BEGIN
Declare @PSF datetime
SELECT @PSF= Max(Finish)
FROM dbo.SelectedEmployeesWorkMode
WHERE (NOT (Finish IS NULL)) AND (DATEDIFF(s, Finish, @St) >= 0) AND (Indx = @Ix)

RETURN @PSF

END

Hi,
I need some help to export the report from MS Access 2010 to MS Word 2010 format. Here are my questions:


a) I see RTF format not the word format during the export?
b) Is there any other way to export the formated report in correct format?
c) Can I fix formating like: 1" space from left etc. during the export?

Thanks,
Amir

HI all,

I want to trigger macro express when a key is pressed in ms-access database. i have developed a database in ms- access which multiple users use. Now when they complete a work they need to send an email to business are confirming the job is done with a formatted excel sheet to which a reply is sought from the business area who will attach some images and send it back to us.

With access i am able to send mail but cannot format the excel sheet the way i want. i have designed a macro to do the desing part so that it goes to the mail and pastes the data in exce in rich text format so that the business are people can drop images beside the data and send it back to us.

to make this possible i should be able to trigger the macro express but that i find it difficult. i can try opening the macro file with sql code but how do i make it trigger?

Is there a way to activate at least a function key from ms- access or is there a way i can tell in my code to paste the data from excel sheet into mail in rich text format.

any ideas please put forward as i need to submit my presentation ASAP.

When Outlook is present we can use the following code to work with Outlook.
Dim olNs As NameSpace
Dim olApp as Outlook.Application
Dim olMi As Outlook.MailItem
Dim olAtt As Outlook.Attachment
Dim Fldr As MAPIFolder

Set olapp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs;getDefaultFolder(olFolderInbox)
Set olMi = appOutlook.CreateItem(olMailItem)
...

But what if not Outlook but Outlook Express is present.
How can it be done to send a message via Outlook Express from Ms Access?

I need to generate a pre-formatted letter from within MS Access, where the name, address and other information will be pulled from the current record on an Access Form.
Basically it is like a welcome letter I could send to any new client I enter into the data base. Since I am doing one letter at the time, mail merge in MS Word is not an option
What would be the easiest way to do this?
Thanks

Hi everyone,

I wonder if you can help me.
I am trying to load a MS ACCESS DB to my hosting, but they do not support it. Therefore, then told me I need to convert MS Access to MYSQL ODBC or something like that.
Has anyone ever done this?
Can you give me tips on how to do this.
Does that change my ASP coding?

Thanks

I have no experience using MS Access to interface with MySQL.
I have a tiny MySQL database that might need to have Access connect with it
over the internet and grab data/update etc.
Is this practical and what does it take to do it?

My project is not time sensitive but I'd like to see what methods I can use
to do remote queries with my database running on a server.


Thanks

On a form with a few columns, i have on top of each column a textbox where the user can fill in a value that will serve as filter for the recordsource of the form.
The tag of each of these textboxes has as value the name of the controlsource of the field of that column.
In the afterupdate event of these textboxes , the function "Setfilt" is called what result in a filtering of the recordsource of the form in accordance with the value entered in the textbox. See code below.

In Access 2.0 this works fine. I converted the program to Access 2007. There the program works fine for all columns, except for columns of datatype True/False ( in Dutch = JA/NEE.)
When the value "JA" or the Value "NEE" is entered in the textbox above such a column i get the following message with as title:"Parameterwaarde opgeven" what translated means : Enter Parameter value"
Below the title stands : "ONWAAR" ( = FALSE) ( e.g when entered "NEE")
and then a textbox to enter the value.
Since the code works fine for all columns except for the true/false columns, I wonder if this is a bug in MS Access 2007?

Private Function getwhere(F, v As Variant) As String

On Error Resume Next
Dim N As Integer
If Len(Trim(v)) < 1 Then v = ""
If v "" Then
Select Case VarType(v)
Case 8 'string
If F = "[Factuurnr]" Then
getwhere = " And " & F & " Like """ & v + """"
ElseIf F = "[jan]" Or F = "[feb]" Or F = "[mrt]" Or F = "[apr]" Or F = "[mei]" Or F = "[jun]" Or F = "[jul]" Or F = "[aug]" Or F = "[sep]" Or F = "[oct]" Or F = "[nov]" Or F = "[dec]" Or F = "[stock]" Then
getwhere = " And " & F & "= " & v
ElseIf F = "[klntID]" Then
getwhere = " And " & F & " =" & Str$(v)
Else
getwhere = " And " & F & " Like """ & v + "*"""
End If
Case 7 'date
getwhere = " And " & F & " = " & changedate(v)
Case Else
getwhere = " And " & F & " =" & Str$(v)
End Select
Else
getwhere = ""
End If
End Function

Function setfilt()

Dim Where As String, frm As Form
Dim N As Integer
On Error GoTo ErrorHandler
Set frm = Screen.ActiveForm
On Error Resume Next
Dim ct As Integer
If frm.Name = "OverzichtOrders" Then
N = 10
ElseIf frm.Name = "frmOverzichtgeleverdeArtikelen" Then
N = 15
Else
N = 12
End If
For ct = 1 To N
Where = Where & getwhere(frm("Value" & LTrim$(Str$(ct))).Tag, frm("Value" & LTrim$(Str$(ct))))
Next
If Where "" Then
Where = Mid(Where, 6)
frm.RecordSource = "Select * from " & frm.Name & " Where " & Where & ";"
Else
frm.RecordSource = frm.Name
End If

Afsluiten:
On Error GoTo 0
Exit Function

ErrorHandler:
Mededeling = foutbericht("setfilt", "modSystem", Err)
Resume Afsluiten
End Function


Not finding an answer? Try a Google search.