DateDiff Function

[Dick7Access]

[FONT=&quot]I am just studying the “dateDiff” function. [/FONT]
[FONT=&quot]On the instructions below it says “Create a query into this table” [/FONT]
[FONT=&quot]1. [/FONT][FONT=&quot]Is he just saying make a query from table x or is there something I don’t know about queries?[/FONT]
[FONT=&quot]2. [/FONT][FONT=&quot]And “type this into field row of the query design grid” Is he saying into a blank grid, or am I missing what field he is talking about?[/FONT]
[FONT=&quot]Instruction in tutorial:[/FONT]
[FONT=&quot]Let's assume a date/time field named StartDateTime to record when the employee clocks on, and another named EndDateTime for when the employee clocks off. To calculate the time worked, create a query into this table, and type this into the Field row of the query design grid:[/FONT]
[FONT=&quot] Minutes: DateDiff("n", [StartDateTime], [EndDateTime])[/FONT]

 

[MarkK]

I don't think there is authoritative language in this regard. Do we "create a query based on a table", or "query records from a table," or, heaven forbid, "extract records from a table as the result of employing a query." Why not "query into a table?" I don't think it's ambiguous at all.

The "Field" row: Look at the first field in the query design grid. Now look to the left of it. You'll see the rows are labelled, with "Field" as the first. There is also a Table row, and a Sort row.

hth

 

[Dick7Access]

I don't think there is authoritative language in this regard. Do we "create a query based on a table", or "query records from a table," or, heaven forbid, "extract records from a table as the result of employing a query." Why not "query into a table?" I don't think it's ambiguous at all.

The "Field" row: Look at the first field in the query design grid. Now look to the left of it. You'll see the rows are labelled, with "Field" as the first. There is also a Table row, and a Sort row.

hth

Thanks for answering. yes I know all the rows are labeled, then I guess I must put it in an empty column of the Field row, and that's what I meant when I said "in a blank grid", I should have said an empty column in field row. It didn't seem right that it should go in a column by itself. . I haven’t worked much with functions. Up until now I have done everything I could with embedded macros. I am trying to revamp all my db, so I am picking lots of different functions and see what they do.

 

[vbaInet]

If you think about it in simple terms you will get it.

Let's say you want to find your real age, i.e. the difference in years between your date of birth and today. You would enter the following (where 19/07/1930 is your date of birth):

DateDiff("yyyy", #19/07/1930#, Date())

... which will yield 85 years

Focus more on what you want to use the function for. Don't get confused by its use in the help files.

 

[Dick7Access]

If you think about it in simple terms you will get it.

Let's say you want to find your real age, i.e. the difference in years between your date of birth and today. You would enter the following (where 19/07/1930 is your date of birth):

DateDiff("yyyy", #19/07/1930#, Date())

... which will yield 85 years

Focus more on what you want to use the function for. Don't get confused by its use in the help files.

The problem was that I have used very little functions in queries. A few times when I did I just copied from somebodies code to get the job done but did not understand what was happening. Now I am just studying. After I understood were to put the function it worked. I don't need it anyplace. What I just tried was to get the second record to calculate, it won't calculate but I like trying and learning as am not up against a clock.

 

[vbaInet]

Slowly but surely.

... am not up against a clock.

Not until your wife wants another recipe db for summer dishes

 

[Dick7Access]

Slowly but surely.

Not until your wife wants another recipe db for summer dishes

Go ahead, rub it in!

I don't get much time for computing when I am on the road, and Lord willing I leave for GA in a week and a half, and when I come back I'll be home maybe three weeks and then head to Carrabele, FL, Slidall, LA, Kilgore, TX, Hallesville, TX and maybe Amarillo, TX. So many restaurants, so few meals.

 

[Dick7Access]

Slowly but surely.

Not until your wife wants another recipe db for summer dishes

Actually right now she wants improvement on a "cents off coupons" db.

 

[vbaInet]

That wasn't a negative comment Dick

She definitely knows how to keep you busy. I wonder how many dbs you have now.

 

[Dick7Access]

That wasn't a negative comment Dick

She definitely knows how to keep you busy. I wonder how many dbs you have now.

Oh! I didn't take it as negative, I should have put a smile face after "rub it in"
In fact I eat up, she is my ego trip.
It is something when she says make it do "such and such", and I say "I can't", and she looks at me with this innocent look, and says "why not"
To me Access is like a cross-word puzzle to some people, except when I figure out my puzzle it has a usefully purpose. She is not the only one that I make db for.
I love helping people and I especially like helping people with Access. In my circle I am the expert. they just don't know it's you guy that are backing me up.

 

[Dick7Access]

That wasn't a negative comment Dick

She definitely knows how to keep you busy. I wonder how many dbs you have now.

Oops I missed the "How Many" line. Last time I counted it was around 28 just for her and total with everybody probable around 100. One of these days I need to learn excel a little better, but every-time I goggle something in access I find something else I didn't even know about.

 

[vbaInet]

Oh! I didn't take it as negative, I should have put a smile face after "rub it in"
In fact I eat up, she is my ego trip.
It is something when she says make it do "such and such", and I say "I can't", and she looks at me with this innocent look, and says "why not"
To me Access is like a cross-word puzzle to some people, except when I figure out my puzzle it has a usefully purpose. She is not the only one that I make db for.
I love helping people and I especially like helping people with Access. In my circle I am the expert. they just don't know it's you guy that are backing me up.

She needs to set you much harder tasks

And don't worry, you can take all the glory

Oops I missed the "How Many" line. Last time I counted it was around 28 just for her and total with everybody probable around 100. One of these days I need to learn excel a little better, but every-time I goggle something in access I find something else I didn't even know about.

That's quite a lot! I think a few years back you mentioned (or I hinted) that you were/should consolidate. Did you make any headway in this regard?

 

[Dick7Access]

She needs to set you much harder tasks

And don't worry, you can take all the glory

That's quite a lot! I think a few years back you mentioned (or I hinted) that you were/should consolidate. Did you make any headway in this regard?

[FONT=&quot]Oh! Absolutely, that's why I don't have an accurate count as I have deleted some after I combined them. Just for my sister alone there was a db for her prison visitation ministry, and one for her prison correspondence course. I was going to include one for Reformers Unanimous class for addiction but she quit. I don't think she used any of them anymore. The db that they had for RU was always crashing, and some people ask me to look at it, and it was a mess. However, the guy that made it was a little touchy about the normalization I wanted to do so I backed off. [/FONT]

 

[vbaInet]

Sounds like you're good with normalisation?

 

[Dick7Access]

That wasn't a negative comment Dick

She definitely knows how to keep you busy. I wonder how many dbs you have now.

The laugh is on me!!!!! I am at the rehab hospital and she wants a db where she can type in the three numbers of the red, green, blue and a field will turn that color. I told her it can't be done, but told her I would ask? If it can be done what do I goggle?

 

[vbaInet]

She's always looking for new functionality
You can look into the RGB() function.

 

[plog]

Its pretty simple, but don't tell her that.

In VBA you can control the color using a Hex string like so:

Me!InputName.BackColor = "000000"


What you need to do is convert her 3 input values into Hex numbers (https://msdn.microsoft.com/en-us/library/963zt96e(v=vs.90).aspx?f=255&MSPPError=-2147217396) and then combine them to get your color string.

 

[Minty]

That's doable

Have a look here http://www.utteraccess.com/forum/Color-Picker-t1762954.html for some inspiration

 

[Dick7Access]

I have goggled everything I can find on Hex function and can't seem to wrap my brain around it. Can somebody give a generic snippet.

 

[plog]

I gave you a link in my prior post. That will convert a decimal to a Hex number.

A color string needs to be 6 characters:

FF9900

The first two (FF) designate the red value, the second two (99) designate green and the last two (00) designate blue. An issue you might run into is that the Hex function doesn't pad 0's onto the left. So Hex(7) will return 7 and not 07. You would have to catch that and pad it with the 0 to make your final string 6 characters.

Also, its a 256 color pallet, meaning if the user inputs 312 for green, its an invalid input. They can only input numbers 0-255 for valid colors.