Null as Character in MS ACCESS 2003 Queries

Hi All,

Table T1:
Column a Column b 1 Sony Nokia 3 Samsung HTC

I have following query

Select * from Table t1
where t1.a Like ("*" & LTRIM(RTRIM([forms]![Search1]![cboInput1])) & "*")

By default when input is empty i need all values.

Here when my prompt value is empty i'm getting only "Nokia and HTC"
But i wish to get all 4 values. Is it possible?

I know in Oracle when i give

Select * from Table t1
where t1.a Like '%%'

This gives all values in Oracle.
But why not in MS ACCESS?

Post your answer or comment

comments powered by Disqus

How to assign Date variable into existing recordset variable and the same date variable should be assigned to [START_DATE] label in forms, it should update date in forms while we add new data . How to resolve using VBA code in MS Access 2003.
Please see the below VBA code for your information.

Private Sub Combo35_AfterUpdate()
' Find the record that matches the control.

Dim RS As Object
Dim sMyVar As Date

Set RS = Me.Recordset.Clone
'MsgBox ("RecordCount: " & RS.RecordCount)

RS.FindFirst "[URL_ID] = '" & Me![Combo35] & "'"

MsgBox (sMyVar)
'RS.FindFirst "[START_DATE] = Null"
'Set RS![START_DATE] = Now()

'MsgBox ("Before Now " & sMyVar)
' sMyVar = Date
'MsgBox ("After Now " & sMyVar)
'If sMyVar Is Null Then RS.[START_DATE] = Now()
'If IsNull(START_DATE) Then MsgBox (" Start date is null")

If Not RS.EOF Then Me.Bookmark = RS.Bookmark

End Sub

Please help me ASAP

hello folks.,
i need to password protect query in ms access 2003 so that users cannot access the queries ?
please tell me how to set that options in ms access 2003..
thank you.

How can I acheive the following in MS Access 2003:

1) Hide the security warning window appears when you I the project/application.

2) Remove the "Microsoft Access" from the title bar and kep the title bar fully empty as exe file.

3) Disable the Alt+Shift to disable the users from viewing the Database Window.

3) Disable the F11 Key


I've got a question about SQL query in MS Access, for example I have the following data:

NAME ____ CITY ____ FAV
vahid ______ NY _______ A
vahid ______ NY _______ B
hamed _____ LA _______ A
hamed _____ LA _______ C

Do I able to merge them like this in MS Access 2003:
NAME ____ CITY ____ FAV
vahid ______ NY _____ A, B
hamed _____ LA _____ A, C

can anyone give any solution to it ? thanks

Im vaidyanathan. I have reports in my project MS Access 2003.
I store images,attachments,documents as ole object.
While generating the report, it takes too much time to generate as the
images is more.
Is there any way to easily solve this problem?
Bcoz there is some issue resolved in MS Access 2003.

Please help me out.

I have developed an application in MS Access 2002 that administers members and donations for a charity organisation. In one of the forms the so called gift aid declarations are maintained. A gift aid declaration consists basically of a from date and an until date. The from date is mandatory, the until date can be Null, meaning that the declaration is open. There may only be one open declaration per person.
To enforce this restriction i wrote a BeforeUpdate trigger on the until date.
Recently i was unpleasantly surprised to discover that in the office that uses my application some serious problems have arised because some persons have more than one open gift aid declaration! How could that be possible? I tested it really thoroughly. After some investigation i found the reason ...

In MS Access 2003, when creating a new record, the BeforeUpdate event on a field is not triggered when the field keeps its default value, Null in this case. But in MS Access 2002 it is! Have the developers of MS Access ever heard of backward compatibility?
First of all, it defies all logic: BeforeUpdate events are meant for validations (why else would there be a Cancel parameter?). For existing records that means indeed that the event should only be triggered on a change, but for new records it should of course always be triggered.
Secondly this has really created serious problems in the administration.
Last but not least, how should i validate field values of newly created records? I would have to use the OnExit event in this case, which is not very appropriate. It is a rather complex validation, and i want to avoid performing it more than necessary.

Does anyone have the same experience? Does anyone know if this change in MS Access 2003 is documented?

I've got a question about SQL query in MS Access, for example I have the following data:

NAME ____ CITY ____ FAV
vahid ______ NY _______ A
vahid ______ NY _______ B
hamed _____ LA _______ A
hamed _____ LA _______ C

Do I able to merge them like this in MS Access 2003:
NAME ____ CITY ____ FAV
vahid ______ NY _____ A, B
hamed _____ LA _____ A, C

can anyone give any solution to it? thanks!

I want to develop multiuser - Activity registry (database) programme in MS Access 2003
My Access programme is enclosed for your review.
The following functions should be done>>
Needed Help1: > > F_ RecordsView:=> should be used to view entered last record of the current user (which user information will come from F_Login )
Needed Help 2 :> F_ RecordsADD: =>should be used to ADD data. When open form, it should be point out the new record in table (T_Records) with form view (F_RecordsADD) and Current User ID

In order to organize the above functions, I have tried to use Form filter functions but it was not work well.
I shall be very much thankful, if you could provide guidance and advice to complete the above mentioned programme.

Note: Combo box functions are work well in my system. So I only expect guidance to link forms with filter records (by User name < => Staff ID)


Im having a problem with MS SQL Server and Access 2003.

I want to have a Date datatype in MS SQL Server, but it is shown as Text in MS Access 2003. Is there a way to correct this?

Can i create a view in MS Access 2003 like a view SQL Server.

Dear MS Access Expert.

Problem: ONE form in my database is acting up and I get the famous MS Access has encountered an Error and needs to close, when I perform the follwing tasks in MS Access 2003 SP3.

Please note this error only occurs with this one form and no other form.

1) This error only occurs when using MS Access 2003 and not 2002. When I do the below operations in 2002 I don't get any problems

3) This error occurs when this form is open in Form View and I try to save.
4) This error occurs when this form is open in Design View and I try to save.
5) This error occurs when this form is open in Design View and then goes to Form View.
6) This error occurs when I try to import this Form from one database to another.

Any ideas or suggestions?
How can I correct this problem so this form behaves in 2003?

Thank you for your help.

How to trough VBA code to create dynamic table in MS Access 2003? Any sample code is welcome. TQ

Dear MS Access Expert,

I have been asked to make proposal for a new database system that will be using SQL Server 2008 as the back end engine.

I would like to develop the Front End in MS Access 2003 since I have been using Access for 9 years.

However, I am not certain how MS Access 2003 is viewed as a Front End building tool in the IT community?

I have read criticism that MS Access can have logic in various places (Macros, Modules, Forms, Queries) which makes it hard to debug.

I agree with the above statement but I do not use Macros and put Form logic on the form's OnOpen event so I don't see the above as too much of an issue.

Can I feel confident telling the company’s IT Director that MS Access 2003 is a competitive Front End building tool for the SQL Server 2008 engine?

Thank you for your response.

Hi all,

We are currently looking at migrating some of our MS Access 2003 databases onto SQL Server in order to speed them up, but we'll still be looking to use Access 2003 as the user frontend.

I have been tasked with displaying one "parameter" (my boss's word, not mine!) from the SQL Stored Procedure in a field on a form using VBA - but I have no clue on how to get started.

Any advice you guys can give would be greatly appreciated - I have the name of the SQL DB, its server location and the name of the SProc and Input Parameter, I just don't know how to get Access talking to SQL to display the information I need.

Many Thanks in advance,


*EDIT* After searching through the rest of forum for Stored Procedure threads/posts, I really can't find anything that helps. In addition to this, I've been asked to carry this out without linking the MSAccess frontend to the SQL backend.*EDIT*

I have encountered problem while trying to retreive chinese character through MS Access 2003 user interface with data stored in MSSQL 2005.

The MSSQL table was import into the Ms Access interface through 'link table' using ODBC. And the column which stored the information was using datatype = nvarchar(510)

I have no problem viewing the chinese character through the SQL server management studio but not in MS Access. In MS Access, the information was displayed as #deleted

Pls help.


The following "Open file dialog box" code allows a user to browse through the folder and save a file at the user specifed location. It works perfectly in MS Access 2003:

Dim strInputFileName As String
Dim strfilter As String
strfilter = ahtAddFilterItem(strfilter, _
"Portable Doument Format (*.pdf)", "*.PDF")
strInputFileName = ahtCommonFileOpenSave(Filter:=strfilter, OpenFile:=False, _
DialogTitle:="Please select a Directory location for your report. Filename is
not require.", _
FileName:="Filename is not required. Click Save to continue", _

However, after upgrading to MS Access 2010. The Open File dialog Box stop working. While in the debugger, every line executes without encountering any errors, but the dialog box will not open. Has anyone experienced a similar problem after upgrading to MS Access 2010. This problem occurrs on a Windows 7, 64bit PC only. Please Advise...


Dear Friends

Can somebody varify the following code for me? It works in MS Access 2003, but not in 2010.
It ask me to debug at line
Set qryDef = CurrentDb.QueryDefs(strQueryName)


	Option Compare Database
Private Sub Command2_Click()
Dim strCrit As String
Dim strCritNO As String     'Criteria for new old
Dim QueryName As String
Dim QueryName2 As String
Dim qryDef As DAO.QueryDef
Dim qryDef2 As DAO.QueryDef
Dim strSelect As String
Dim strSelect2 As String
strCrit = Me.cobCrit
strCritNO = Me.FirstDate
QueryName = "000MainService"
QueryName2 = "111OtherOR"
strNewT = newTable
strQueryName = QueryName
strQueryName2 = QueryName2
strSelect = "SELECT " & strCrit & " AS Period,"
strSelect = strSelect & strCritNO & " AS 1stDate,"
strSelect = strSelect & "[0000MainService].Code, [0000MainService].CaseMx, [0000MainService].Sex, [0000MainService].Age,
[0000MainService].Risk1, [0000MainService].Risk2, [0000MainService].ProjSite, [0000MainService].Project,
[0000MainService].Staff, [0000MainService].DorO, [0000MainService].Diagnosis, [0000MainService].DrugSex,
[0000MainService].NDist, [0000MainService].SDist, [0000MainService].NSReturn, [0000MainService].DWater,
[0000MainService].Condom, [0000MainService].DCSL, [0000MainService].FUCSL, [0000MainService].PreCSL,
[0000MainService].Testing, [0000MainService].PostCSL, [0000MainService].FCSL, [0000MainService].PSC, [0000MainService].YCSL,
[0000MainService].GCSL, [0000MainService].Referral, [0000MainService].To, [0000MainService].Fr, [0000MainService].For,
[0000MainService].Meal, [0000MainService].HE, [0000MainService].Remark, [0000MainService].BHCID,[0000MainService].Dx1  from
0000MainService  "
strSelect2 = "SELECT " & strCrit & " AS Period,"
strSelect2 = strSelect2 & "OtherOR.ORID, OtherOR.ProjSite, OtherOR.Project, OtherOR.ClientType, OtherOR.Male, OtherOR.Female,
OtherOR.Condom, OtherOR.HE, OtherOR.Meal, OtherOR.Referral, OtherOR.To, OtherOR.For, OtherOR.Staff, OtherOR.Remark FROM
Set qryDef = CurrentDb.QueryDefs(strQueryName)
Set qryDef2 = CurrentDb.QueryDefs(strQueryName2)
qryDef.SQL = strSelect
qryDef2.SQL = strSelect2
'DoCmd.OpenQuery QueryName, acNormal, acEdit
'DoCmd.OpenQuery QueryName2, acNormal, acEdit
End Sub

I created a query in ms access. In criteria a put [Enter a value : ] . I created a form of this query. When i open this form it shows a input box with “ Enter your value : “ when i put a value in this box and click ok. it shows result. but if the input value doesn’t exist this database then open a blank form.

My question is, can i open a design form if the input value doesn’t exits. Actually i want to when input value match it’ll show result form but when the input data doesn’t exist the database it’ll show a design form.

Please help me anyone if need VB code or easy method for this task.

In Ms Access 2003 Ctrl Break would stop the code on the line after and action query had completed.

In Ms Access 2010 Ctrl Break stops the code on the line that runs the action query interupting the query.

This seems to leave to database data in an unkown status.

What is the status of the data the query was to change?
Not changed or Partially Changed

What happends if you executed the same line and run the query again?

Is there any documentation about this changed behaviour of Ctrl Break.
Is there a fix or a work around?

Thank you,

I have developed an accounting db in ms access 2003 but am having a small problem with a part of it.
I have a table tblAccounts with the name of the account, the account ID, openingBalance and the currentBalance fields.
The operations involved are
Money can be withdrawn from the account
Money can be deposited in the account
To handle this I have four tables

tblWithdrawal and tblWithdrawalDetails to handle the withdrawals
tblDeposits and tblDepositDetails to handle the deposits
All the forms are there and update queries to handle the transactions

What I want is the user to view all the transactions and the Balance before the accounts were updated e.g asumming the account balance is $1000 and a withdrawal was done of $100 then a deposit was made of $ 200 then the user will view that the balance was $1000 then $900 and the current balance is $1100.
Short of storing the totals in a table I cant figure how to do this


I need to display the out put of a table as chart in ms access 2007. My table is as below.

ItemID Week 1 Value Week 2 Value 1001 87 5 1002 80 1 1003 42 1 1004 55 164
In the chart X co ordinate should be the "ItemID" and the Y co ordinate should be the week number (eg, week1, week2 ...etc).
Is it possible to display the chart with the above table? Or do I need to transform the table and then dosplay as chart?

Also can I transform the above table as shown below using a single Transform query ?

ItemID Week Value 1001 1 87 1001 2 5 1002 1 80 1002 2 1 1003 1 42 1003 2 1 1004 1 55 1004 2 1664
Also please let me know if I can transform the table as above is it possible to create the chart.

Please help me resolving these.

I am trying to create a Macro In MS Access xp to automatic start Lotus notes. The issue is in Lotus Notes, You must enter your password to continue. If I already have lotus Notes r5 open, The macro will send the auto email to the appropriate folks. then shut down notes. What I would like to happen is the macro to open notes, Key in my password, send the emails on schedule, and shut down lotus notes. Please Help!!!

I've some problem when I connected to Sybase with ODBC and make a link table in MS Access 2003, Thai charset is not displayed correctly but It works in MS Access 97. Can anyone help me??

Hey guys

Not sure if this is the appropriate forum but let me give it a go anyways.

I’m trying to simulate triggers in MS Access 2003 from a .NET platform using C#. Is there anybody out there that perhaps faced the same challenge and if so can you please give me some guideline where to start.

Any background, information of guidelines will be highly appreciated.



Not finding an answer? Try a Google search.