FredixBlog

Quertion: I have a query I’m having a problem with. In SQL Server, a person can use a star next to a column so if there is no match it returns a null value. However, in Access it will not allow it. There are other conditions as well so I can not use a simple outer join. Here is an example.
Table A, Clients
Name, account, state
Table B, Accounts
Account, Registration
The example statement would be
select a.name, a.account from Clients a, account b where a.account=*b.account and a.state=’AZ’
What I want is if a client does not have an account then it shows up with a null value. What statements needs to be used in Access?
Fredix gave this response on 3/16/2002:
Hi!

Outer joins are possible in Access as well.
In the query builder, double-click on the relation line. A window with three options will pop-up. Choose the second or the third option, close the box and try your query.

I personnaly never understood the text describing the second and third option. Just give a try until the relation line shows an arrow on the table where there can be no related record (Account in your case).

Regards.

Random Posts

Quertion: Can anyone figure out a little function that will clear all line breaks (Chr(10)+Chr(13)) in a string?
Matt(+)
Fredix gave this response on 1/17/2002:
Hi!

Here is a function I use:

Public Function ClearBreaks(TheString)
dim ch, result as string
dim i as integer

    result = ""
    if len(TheString) = 0 then
        ClearBreaks = ""
        exit function
    end if

    for i = 1 to len(TheString)
        ch = mid(TheString,i,1)
        if ch <> chr(13) and ch <> chr(10) then
            result = result & ch
        else
            if ch = chr(13) then result = result & " "
        end if
    next i

    ClearBreaks = result

end function

With this, you will have the line breaks replaced by a space.

Regards.

• Truncate long path names
• Copy a file
• Create ODBC DSN
• Relink all tables
• Gibson plus solide que Fender?
• Display a counter and remaining time in a loop
• How can I e-mail a file from Microsoft Access?
• Create a linked table with VBA
• Coordinates of the third point
• Message to log file

Quertion: Hi
I am trying to pass a simple command line parameter to my Access Application. In my Shortcut I have written this:
“M:\\MyApp.MDB” /Cmd “Success!”
and in the actual application the first line of code displays what’s in the command line. ie:
msgbox Command(),vbInformation
———————————
All I am getting are blank lines.
Any Suggestions,
Many thanks
Ted
Hi!

There is a very simple explanation:
Your command is:

“M:\\MyApp.MDB” /Cmd “Success!”

So you call MyApp.mdb, which is associated with Access, so, as expected, access launches the MDB as expected, but in the file association process, it looses all the rest, thus your blank line.

The easiest is to enter the command like this:

“c:\\program files\\microsoft office\\office\\msaccess.exe” /cmd Success!

Or whereever you have msaccess.exe installed.

There is another solution, which implies messing the registry. Just post a follow up and I can start trying: I LOVE messing the registry.

Regards.

Fredix gave this follow-up answer on 1/25/2002:
Hi! Me again!

I couldn’t resist to try the registry trick.

1) Click start/run, type regedit, click ok

2) If you are not familiar with the registry or abused with the bourbon, just concentrate and make no mistake. There is no Undo command.

3) Open the HKEY_CLASSES_ROOT key and browse down until you find the .mdb key. Open it.

4) Depending on your version of Access, you have: Access.Application.9 for Access 2000 or Access.Application.8 for Access 97.

5) The previous step was only for your culture. There is nothing to do here, just check its here.

6) In Edit menu, click find. Enter Access.Application.8 (97) or Access.Application.9 (2000). Click Find Next

7) Open this found key and look for a key named ‘Command’. Its under Access.Application.8\shell\open\

On the right part of the screen, click on the red icon. The value looks like this

I:\\Program Files\\Microsoft Office 97\\Office\\MSACCESS.EXE /NOSTARTUP “%1″

9) Note that my theory is correct: There is only one parameter taken here. You should add two more: One for the /cmd, and one for the “success!”

10) Change the value so that it is:

I:\\Program Files\\Microsoft Office 97\\Office\\MSACCESS.EXE /NOSTARTUP “%1″ %2 %3

Note there is no ” with the %2 and the %3

11) Close your editing box and try your command: “M:\\MyApp.MDB” /Cmd “Success!”

12) Guess what is displayed in your message box?

You can do the same with Access.Application.9 if you have Access 2000.

I hope this makes sense to you.

Regards.

Random Posts

Quertion: I try to develop Point of Sales program by access. I have trouble in connecting the program with the tools such as cash drawer. My question is that if on click event I want to drawer to activate (Open) How do I?
Drawer connect to PC using serial port
Thanks
Fredix gave this response on 4/3/2002:
Hi!

You have two choices: The simplest is to use .bat files that sends the appropriate commands. You call the .bat with the shell() function.

More sophistcated and better method is to use the MsComm control. It contains all the features for controlling the serial ports.
Look at this page for a good example:

http://www.freevbcode.com/ShowCode.Asp?ID=2236

Regards.

Fredix gave this response on 4/3/2002:
Here is an example. There is only a three line .bat file, called print.bat:

mode com1:19200,n,8,1
type c:\\caisse\\pfile > com1
exit

When I need to send some characters to the peripherals, I build the c:\caisse\pfile like this:

Open “c:\\caisse\\pfile” For Output As 1
Print #1, Chr(27) & “=” & Chr(1);
Print #1, Chr(27) & “p” & Chr(0) & Chr(20) & Chr(20);
Close 1

Finally call the print.bat, which sends the file to Com1

Shell(“c:\\caisse\\print.bat”, 7)

This method works quite well and is easy to implement. However, the problem is that the DOS window remains on the screen. To avoid this, the first time the window is here, right-click the top of the window, choose properties and check the box ‘close on exit’.
I would prefer using MSComm control, but the app was in Access 2.0, so I had no choice. The advantage with MSComm is that you can check if the peripherals are ready. This is important as this program must run the whole day long, and there is a client in front of the user.

Good luck with your program

Random Posts

Quertion: One more question:
What is the max number of users in an Access 97 Database ?
Thanks,
Paul
Fredix gave this response on 4/5/2002:
Hi!

Access 97 allows 128 users connected. Its however good practice to consider Access for a minimum of users. The more users there are working at the same time, the more network traffic is involved, as its file based, not CS.
So, expect that the performance goes down as the users increases.

Regards.

Random Posts