Hi, could someone please help me? I am a novice so please try to be patient.
I've created a database to hold members who join my website. I've created the form to enable them to join and their data goes into the database. I've managed to use ColdFusion to display a list of all members.
But I want to refine this list, to only show members who joined this year. How do I do this? Becuase I've tried everything I can think of.
Here's my current code for the page that produces the list of those in who joined in 2007: - <!---newmemberslist.cfm list of all members joined in 2007--->
-
-
<html>
-
<head>
-
<title>Admin Area - List all Members joined in 2007</title>
-
</head>
-
-
<body bgcolor=beige>
-
<CFQUERY name="newmemberslist" DATASOURCE = "jpkelle2-access">
-
SELECT memberID, forename, initial, surname, sex, dob, address, town, county, postCode, email, joinDate
-
FROM members
-
WHERE joinDate > 01/01/2007
-
</CFQUERY>
-
-
<h1><center>Members</h1>
-
<h2>New Members in 2007</h2>
-
-
<p>
-
<table border=1 bgcolor="beige" cellpadding="3" cellspacing="0">
-
<tr>
-
<th>Member ID</th>
-
<th>Name</th>
-
<th>Sex</th>
-
<th>Date of Birth</th>
-
<th>Address</th>
-
<th>Email</th>
-
<th>Date Joined</th>
-
</tr>
-
-
<CFOUTPUT Query="newmemberslist">
-
-
-
<tr>
-
<td><center>#memberID#<center></td>
-
<td width="15%">#forename# #initial# #surname#</td>
-
<td>#sex#</td>
-
<td>#dob#</td>
-
<td>#address#, #town#, #county#, #postCode#</td>
-
<td>#email#</td>
-
<td>#joinDate#</td>
-
</tr>
-
-
-
-
-
</CFOUTPUT>
-
-
</table>
-
-
<hr><p>End of members list.</p>
-
-
</body>
-
</html>
When I execute this it doesn't seem to treat '01/01/2007' as a date, even though I've used the format 'date/time' in Access.
Is it a problem with my code or with my Access design?
thanks, James
4  2937  acoder 16,027
Recognized Expert Moderator MVP
James, welcome to TSDN.
Try the year function: - <CFQUERY name="newmemberslist" DATASOURCE = "jpkelle2-access">
-
SELECT memberID, forename, initial, surname, sex, dob, address, town, county, postCode, email, joinDate
-
FROM members
-
WHERE year(joinDate) = 2007
-
</CFQUERY>
Tip: why not make a generic page, e.g. either through a url variable, or use year(now()) to get the members who joined in the current year, so you don't need to change it every year!
thanks so much for that.
it works, but......the displayed data has rearranged the date format to
yyyy-mm-dd
why has it done this? can I not get it to be dd-mm-yyyy again?
also, how can I get rid of the time next to the date
(eg it displays - 2007-01-12 00:00:00.0) I don't want the time to be displayed.
Can I not get rid of it seeing I have the field as 'date/time' in Access?
Does it not really matter if a date in Access is defined as 'text' in the data type column, rather than date/time.
Because if I set joinDate as 'text' in Access then it works OK. I get just the date and not the time as well, and I get in the format dd/mm/yyyy, which is what I want.
Is it OK that I've used the text format rather than date/time, or will this produce errors somewhere along the line?
acoder 16,027
Recognized Expert Moderator MVP
Does it not really matter if a date in Access is defined as 'text' in the data type column, rather than date/time.
Because if I set joinDate as 'text' in Access then it works OK. I get just the date and not the time as well, and I get in the format dd/mm/yyyy, which is what I want.
Is it OK that I've used the text format rather than date/time, or will this produce errors somewhere along the line?
No keep it as date/time and use the coldfusion dateformat function. You can use a mask, e.g. "dd/mm/yyyy" to format the date as you wish.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: English Teacher |
last post by:
Which would be more useful to learn, PHP or COLDFUSION? I know
Coldfusion is popular in the work force. Is PHP?
Thanks!
|
by: kuhni |
last post by:
Hi everybody!
Writing this time, I'm really desperate. Basically, I have a
conceptual problem of how to solve a certain "problem" in MS Access
97.
General objective:
In order to categorise certain products, I need to know whether a
specific product has been promoted in a specific week. To find this
out, I have a linked table, which has all promotions for each period
|
by: acoder |
last post by:
How to Upload a File in Coldfusion
Use the cffile tag for uploading files to the server.
Note that allowing people to upload files is fraught with danger and only trusted users should be allowed to upload files. Checks should be made to make sure that only allowed file types are uploaded.
The Client-Side
First of all, let us deal with the client side. This assumes some knowledge of HTML.
|
by: Victor |
last post by:
I have a strange problem in my website. I configured my website to run under
2 worker processes. (web garden enabled). and I stored my user information
in the current httpcontext(like
Httpcontext.current.items.add("__currentuser", myUserobject") and retrieve
the user object like (user =
(UserType)Httpcontext.current.items). the website
authentication based on the form authentication. and I used login control
and customized the validation...
|
by: James890 |
last post by:
I want to enable a user to enter a start and end date to define the period they want to search for records of members who joined on certain dates. Funny thing is...I've got it to work half of the time. For e.g. I have 4 records between 26/10/2005 and 1/08/2006. When I enter 01/01/2005 as startDate and 31/08/2006 as endDate, I get the 4 records. However, if I alter the endDate to 01/09/2006 I get every record in the database!!!??? Why's this? I...
| | |
by: jesmi |
last post by:
my code is:
addRecord.cfm
<cfinclude template="head.cfm">
<p>
<table width="100%" border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td>
<div align="center">
|
by: kmitchell00 |
last post by:
I am coding in Coldfusion MX7 and using Javascript for some of the functionality. The basic functionality I'm coding is, based on a value the user chooses from a dropdown box, I populate address fields with the associated data. I am using the onChange attribute of the dropdown box for the call to a Javascript function. I prepopulate a Coldfusion array with the address data and am using cfwddx to convert the Coldfusion array to a Javascript...
|
by: Blackmore |
last post by:
Following the guidance contained in Dreamweaver, I have declared a cflogin tag in the application.cfc file. This declaration contains an idletimeout setting of 10 seconds (N.B. 10 seconds for development purposes only).
<cflogin idletimeout="10">
<cfif Not IsDefined("cflogin")>
<cfinclude template="./loginform.cfm">
<cfelse>
{Authorisation functions}
</cfif>
|
by: johnVarma |
last post by:
Hi All,
Iam facing a problem with bar chart using coldFusion.
if there is only one <cfchartseries> tag then the seriesLabel attribute is not displaying instead of that the items of the cfchartdata are displaying.
if there are more then one <cfchartseries> then the seriesLabel specified is displaying properly.
Please look at the code and its corresponding bar chart attached .
Code -1 and BarChart1 :
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
