Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

Forums     

Microsoft Access Forum
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
This forum is for the discussion of Microsoft Access.

Access is my personal specialty. Have questions? Comments? Want to discuss how to do something? Post it here. Also, I get a LOT of questions sent to me in Email from people around the world. I'll post the interesting ones in here. Feel free to comment on them.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft access forum
Post New Topic

Read Is there a Restricted number of linked DB by Dottie A @ Thu 11/8
If I create 1 main DB and then have 12 separate databases that link tables to it, is that possible or even recommended?  If not, what are my alternatives?


Reply from Alex Hedley:

How many Tables will you be linking?
There is an Access limits article from MS which likely tells you this but 12 should be fine.
The next option is to upgrade to MSSQL Server and pull the data from that. Create Views to join your Tables, Queries in Access.
Show Just This Thread        Post Reply
Read Booking calendar by Vesna D @ Thu 11/8

Can you make a lesson that would relate to the booking calendar in the hotel? Thank you


Reply from Alex Hedley:

There is a Calendar Seminar

The Access Work Order Seminar also deals with Scheduling, this might be useful too.
Show Just This Thread        Post Reply
Read Pasting from Excel by Jeremiah T @ Wed 11/7
Why is pasting from excel proving to be so difficult?  I get a gamut of error messages no matter what I do.
If i just copy the specific cells, I get this error--- "The value you entered isnt valid for this field"
If I try to copy a whole row, I get this error--- "Too Many Fields Defined"

These errors make sense as a sum of the words, so ive made sure that what Im pasting is formatted identically to the  fields in Access.  Im at a loss.  please help..


Reply from Alex Hedley:

Do the columns you have in your Excel workbook match the fields in your Access Table?
They need to be the same order.
Show Just This Thread        Post Reply
Read conditionally Format continuous subform by Gina D @ Mon 11/5
Hi Alex/Richard,
I would like to conditionally format a field in a continuous subform located on a Main Form.
VERY Basic Layout:
Employee Profile is the Mainform   EmployeeID as Primary Key
TrainingNeedsSubF - Continuous Subform, Record Source is from NeedsQ, CourseNameID is a field in the form.
EducationSubF - Continuous Subform, Record Source is from EducationQ, CourseNameID is a field in the form.
I would like the TrainingNeedsSubF CourseNameID to highlight GREEN if the CourseNameID on the EducationSubF is the same.
Unfortunately, I am unable to change any of the forms format, they have to remain as continuous forms, I have dependent comboboxes.
Your guidance would be appreciated.
Regards
Gina D



Reply from Alex Hedley:

What Formatting having you tried so far?
Show Just This Thread        Post Reply
Read multi select list boxes by RT @ Mon 11/5
Using your customer database as an example, you might have a customer who belongs to several groups.
Having a multiple select list box would allow you to quickly see which groups that customer belongs. The data would usually end up in a junction table, but then you can later find all of the customers that belong to a specific group.
That would have a lot of value.
Show Just This Thread        Post Reply
Read Imaging Seminar by HUGO M @ Sun 11/4
Please share the solution, I'm having the same problem.


Reply from Alex Hedley:

Can you confirm you are using a 64bit version of Access?
Does the sample db not work for you either?
Show Just This Thread        Post Reply
Read divide two querries then group by department by jannette j @ Fri 11/2
I am looking for the rate change between new accounts and all accounts, I have both queries listed below. I need to divide Emergeny Work Orders by AllWorkorders, take that percentage and group by department in the same query. Thanks
Expand|Select|Wrap|Line Numbers
The first query:

SELECT maximoreport.lfc_assetdept,
       Sum(LEFT([actuallaborhours], Instr(1, [actuallaborhours], ':') - 1) + (
           RIGHT(
           [actuallaborhours], Instr(1, [actuallaborhours], ":") - 1) ) / 60) AS
       LaborHours
FROM   maximoreport
WHERE  ( ( (( [actualstartdate] - [reporteddate] )) < 3.5 )
         AND ( ( maximoreport.[estimated labor hours] ) = "00:00" )
         AND ( ( maximoreport.worktype ) = "cm"
                OR ( maximoreport.worktype ) = "em"
                OR ( maximoreport.worktype ) = "mmnro"
                OR ( maximoreport.worktype ) = "mmroi"
                OR ( maximoreport.worktype ) = "pmcm"
                OR ( maximoreport.worktype ) = "pmins"
                OR ( maximoreport.worktype ) = "pmor"
                OR ( maximoreport.worktype ) = "pmpdm"
                OR ( maximoreport.worktype ) = "pmreg"
                OR ( maximoreport.worktype ) = "pmrt" )
         AND ( ( maximoreport.actualstartdate ) >=
               Dateadd("h", -1, [enter the start date])
               AND ( maximoreport.actualstartdate ) <
                   Dateadd("h", 23, [enter the end date]) ) )
        OR ( ( ( maximoreport.worktype ) = "em" )
             AND ( ( maximoreport.actualstartdate ) >=
                   Dateadd("h", -1, [enter the start date])
                   AND ( maximoreport.actualstartdate ) <
                       Dateadd("h", 23, [enter the end date]) ) )
GROUP  BY maximoreport.lfc_assetdept
ORDER  BY maximoreport.lfc_assetdept;



Query 2:
Expand|Select|Wrap|Line Numbers

SELECT maximoreport.lfc_assetdept,
       Sum(LEFT([actuallaborhours], Instr(1, [actuallaborhours], ':') - 1) + (
           RIGHT(
           [actuallaborhours], Instr(1, [actuallaborhours], ":") - 1) ) / 60) AS
       LaborHours
FROM   maximoreport
WHERE  ( ( ( maximoreport.worktype ) = "cm"
            OR ( maximoreport.worktype ) = "em"
            OR ( maximoreport.worktype ) = "mmnro"
            OR ( maximoreport.worktype ) = "mmroi"
            OR ( maximoreport.worktype ) = "pmcm"
            OR ( maximoreport.worktype ) = "pmins"
            OR ( maximoreport.worktype ) = "pmor"
            OR ( maximoreport.worktype ) = "pmpdm"
            OR ( maximoreport.worktype ) = "pmreg"
            OR ( maximoreport.worktype ) = "pmrt" )
         AND ( ( maximoreport.actualstartdate ) >=
               Dateadd("h", -1, [enter the start date])
               AND ( maximoreport.actualstartdate ) <
                   Dateadd("h", 23, [enter the end date]) ) )
GROUP  BY maximoreport.lfc_assetdept
ORDER  BY maximoreport.lfc_assetdept;

Show Just This Thread        Post Reply
Read time sum by jannette j @ Tue 10/30
I have a query where i am trying to calculate the total hours worked on an order.But when i run it i get an error  This expression is typed incorectly or is too complexed to be evualated does anyone know how i can do a query that adds hours.

SELECT ( Sum(Datepart("h", [actuallaborhours]) * 3600 +
                      Datepart("n", [actuallaborhours]) * 60) )   AS
       TotalSeconds,
       Round([totalseconds] / 3600, 0) & ':' & RIGHT("00" &
                                               Round(( ( totalseconds /
                                                         3600 ) - Round(
                                                       totalseconds / 3600
                                                       , 0) ) *
                                                     60, 0
                                               ), 2) AS TotalTime,
       [maximo report].worktype,
       [maximo report].actualstartdatefrom
       [Maximo Report]
WHERE  ( ( ( [maximo report].worktype ) = "cm"
            OR ( [maximo report].worktype ) = "em"
            OR ( [maximo report].worktype ) = "mmnro"
            OR ( [maximo report].worktype ) = "mmroi"
            OR ( [maximo report].worktype ) = "pmcm"
            OR ( [maximo report].worktype ) = "pmins"
            OR ( [maximo report].worktype ) = "pmor"
            OR ( [maximo report].worktype ) = "pmpdm"
            OR ( [maximo report].worktype ) = "pmreg"
            OR ( [maximo report].worktype ) = "pmrt" )
         AND ( ( [maximo report].actualstartdate ) >=
               Dateadd("h", -1, [enter the start date])
               AND ( [maximo report].actualstartdate ) <
                   Dateadd("h", 23, [enter the end date]) ) );



Reply from Alex Hedley:

Try splitting the query into simpler ones, sometimes access needs the calculation done first before it can then use it as a value somewhere else.
Show Just This Thread        Post Reply
Read Decimels by Gerald S @ Tue 10/30
In Access how can I get a decimal in the drop down Field window In Office 365?It only has "long numbers" and "numbers".


Reply from Alex Hedley:

Theres another property on number fields for Decimal Places or Format.
Show Just This Thread        Post Reply
Read sum short time field by jannette j @ Tue 10/30
Does anyone know how i can sum a short time field .


Reply from Alex Hedley:

Is this because you are then displaying the sum as a time and it isn't showing correctly? Check your formatting of the HH instead of hh, there are different formats for 12hr/24hr and total hours.
Show Just This Thread        Post Reply
Read insert values from multiple queries by jannette j @ Mon 10/29
i have alot of queries that calculate different values . I have a form which is unbound to anything and in that form it has textboxes. I need to insert the values from the queries into the text boxes on the fly do you know how i can do this using VBA perferably on open form event.





Reply from Alex Hedley:

DLOOKUP
Show Just This Thread        Post Reply
Read Search Box by Sara P @ Mon 10/29
Hi Richard, I have followed your Dev 8-3 regarding Search Box and everything is spot on to your example. The problem is, I don't come past the point in my form of entering the first letter, to filter the drop down list. As soon as I click on the first letter, the whole list disappears, even if there is a letter in the list the same as I have typed. Please help! Sara


Reply from Alex Hedley:

Is the name of the textbox the control you are passing to the query?
Show Just This Thread        Post Reply
Read Edit Fields by lauro c @ Sun 10/28
Alex: when I want to change the size of a field in a form, the rest of the fields follow. How can I override this default so I can edit each field without the rest of the fields. Thasnks much.


Reply from Alex Hedley:

Lauro I'm not sure what you mean, you are selecting a textbox on a form, changing the size as in width/height or font size?
Show Just This Thread        Post Reply
Read multi select list boxes by Richard R @ Sat 10/27
Question: What purpose does a combo box with multiple value selections have? I can set it up and get it displayed in a form correctly, but I have a hard time what purpose this serves in a database. Does it act as like a sub-table for lack of better terms. I ask because I am attempting to create a training attendance tracking and documenting database. If I select a training and have a combo box that I can select many names that would easily fulfill my need; however, I cant seem to find some functionality that would all for "bean count" of all the names selected across the various training sessions. Im not sure if this really counts as a question or not, but I appreciate any information you can clarify. Thank you.

---

Answer: Honestly, I don't use them that much. I do cover them in a couple of lessons, most notably Access 321:

http://599cd.com/XAC321

But if you do have a need to use them, they can come in handy. You do need to use some VBA code to save the selected values in an underlying table, which can be quite tricky. I'll be covering them again in another lesson soon.
Show Just This Thread        Post Reply
Read uDialog Developer 11 by James G @ Sat 10/27
Looks like this could be a basis for database specific error handling process rather than depending on access generated messages...No?


Reply from Alex Hedley:

Do you mean like my tip Custom Error
Show Just This Thread        Post Reply
Read Navigation form by Piet n @ Wed 10/24
Hi I have Navigation form with more than two sub-forms, now I am trying to link to Master Form I don't know how
your assistance will be appreciated


Reply from Alex Hedley:

Have you opened up the Property Sheet, they are in there.
Show Just This Thread        Post Reply
Read Video Embed by Richard L @ Thu 10/18
I've played around and found on the web, how to embed a video media player.  It works great.  But I'd like to create a button that moves the track bar 10 seconds and another that skips the track bar ahead 30 seconds.  What is the code for that?


Reply from Alex Hedley:

Depends what code you used to embed a video, what did you use?
Show Just This Thread        Post Reply
Read New Class by Damien N @ 10/16/2018
Working through Access Expert 15, it occurs to that it would be useful to add a new Class and load all current students into the form. Is this possible?

Thanks


Reply from Alex Hedley:

You could run an INSERT statement that loops the students and adds them to the Class Table.
Show Just This Thread        Post Reply
Read Imaging Seminar by Frits d @ 10/15/2018
7/11/2018
Dear Alex, I like to send you a .ppt file with my answer. I hope that it is convenient to you and to understand my question.Greatings,
Frits de Beer


Reply from Alex Hedley:

It's easier to upload the file to a sharing site like google drive, dropbox, imgr etc and share a public link
Show Just This Thread        Post Reply

Show Older Posts         Collapse All Topics

 

Post Your Comments or Subscribe
    If you would like to be notified of new posts on this forum,
    just enter your email address below. It will be kept private.
 
If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 6+1:
  
  Notify me when the this forum is updated
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE
7/31/2018Microsoft Access Developer 8
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP